Инн в экселе какой формат
Вам когда-нибудь приходилось импортировать или вводить в Excel данные, содержащие начальные нули (например, 00123) или большие числа (например, 1234 5678 9087 6543)? Это могут быть номера социального страхования, телефонные номера, номера кредитных карт, коды продуктов, номера счетов или почтовые индексы. Excel автоматически удаляет начальные нули и преобразует большие числа в экспоненциальное представление (например, 1,23E+15), чтобы их можно было использовать в формулах и математических операциях. В этой статье объясняется, как сохранить данные в исходном формате, который Excel обрабатывает как текст.
Преобразование чисел в текст при импорте текстовых данных
Для форматирования отдельных столбцов в виде текста при импорте данных в Excel используйте функцию Получить и преобразовать (Power Query). В этом случае импортируется текстовый файл, однако данные проходят те же этапы преобразования, что и при импорте из других источников, таких как XML, Интернет, JSON и т. д.
Откройте вкладку Данные, нажмите кнопку Получить данные и выберите вариант Из текстового/CSV-файла. Если вы не видите кнопку Получить данные, выберите Создать запрос > Из файла > Из текста, найдите нужный файл и нажмите кнопку Импорт.
Excel загрузит данные в область предварительного просмотра. В области предварительного просмотра нажмите кнопку Изменить, чтобы загрузить Редактор запросов.
Если какие-либо столбцы нужно преобразовать в текст, выделите их, щелкнув заголовок, затем выберите Главная > Преобразовать > Тип данных > Текст.
Совет: Чтобы выбрать несколько столбцов, щелкните их левой кнопкой мыши, удерживая нажатой клавишу CTRL.
В диалоговом окне Изменение типа столбца выберите команду Заменить текущие, и Excel преобразует выделенные столбцы в текст.
По завершении нажмите кнопку Закрыть и загрузить, и Excel вернет данные запроса на лист.
Если в дальнейшем ваши данные изменятся, на вкладке Данные нажмите кнопку Обновить, и Excel их автоматически обновит и преобразует.
В Excel 2010 и Excel 2013 импортировать текстовые файлы и преобразовывать числа в текст можно двумя способами. Рекомендуется использовать Power Query (для этого нужно скачать надстройку Power Query). Если надстройку Power Query скачать не удается, можно воспользоваться мастером импорта текста. В этом случае импортируется текстовый файл, однако данные проходят те же этапы преобразования, что и при импорте из других источников, таких как XML, Интернет, JSON и т. д.
На ленте откройте вкладку Power Query и выберите Получение внешних данных > Из текста.
Excel загрузит данные в область предварительного просмотра. В области предварительного просмотра нажмите кнопку Изменить, чтобы загрузить Редактор запросов.
Если какие-либо столбцы нужно преобразовать в текст, выделите их, щелкнув заголовок, затем выберите Главная > Преобразовать > Тип данных > Текст.
Совет: Чтобы выбрать несколько столбцов, щелкните их левой кнопкой мыши, удерживая нажатой клавишу CTRL.
В диалоговом окне Изменение типа столбца выберите команду Заменить текущие, и Excel преобразует выделенные столбцы в текст.
По завершении нажмите кнопку Закрыть и загрузить, и Excel вернет данные запроса на лист.
Если в дальнейшем ваши данные изменятся, на вкладке Данные нажмите кнопку Обновить, и Excel их автоматически обновит и преобразует.
Применение пользовательского формата для сохранения начальных нулей
Если эту задачу необходимо решить в пределах книги, поскольку она не используется другими программами в качестве источника данных, для сохранения начальных нулей можно использовать пользовательский или специальный формат. Этот способ подходит для цифровых кодов, содержащих менее 16 цифр. Цифровые коды также можно отформатировать с использованием дефисов или других знаков препинания. Например, чтобы сделать телефонные номера более удобными для чтения, можно добавить дефисы между международным кодом, кодом области, префиксом и остальными цифрами.
Всем привет, подскажите есть ли регулярные выражения для проверки ввода в ячейку ИНН (10 или 12 символов)? Или других реквизитов (СНИЛС, ОКПО, ОКАТО и тд)
Желательно без использования макросов и VBA. Но буду рад любым вариантам.
Поиск не дал результатов.
Всем привет, подскажите есть ли регулярные выражения для проверки ввода в ячейку ИНН (10 или 12 символов)? Или других реквизитов (СНИЛС, ОКПО, ОКАТО и тд)
Желательно без использования макросов и VBA. Но буду рад любым вариантам.
Поиск не дал результатов. Coolibin
Через проверку данных или ДЛСТР.точнее после прочтения правил.особенно п3.там синеньким подчеркнуто китин
Если я правильно Вас понял, то достаточно знать формат, чтобы допускать, что в ячейку введен тот или иной реквизит.
Например 10 знаков = ИНН юр лица, 12 знаков = ИНН физ лица/ИП, 11 знаков в формате 111-222-333 44 - СНИЛС и т.д.
Если я правильно Вас понял, то достаточно знать формат, чтобы допускать, что в ячейку введен тот или иной реквизит.
Например 10 знаков = ИНН юр лица, 12 знаков = ИНН физ лица/ИП, 11 знаков в формате 111-222-333 44 - СНИЛС и т.д. ArkaIIIa
Если я правильно Вас понял, то достаточно знать формат, чтобы допускать, что в ячейку введен тот или иной реквизит.
Например 10 знаков = ИНН юр лица, 12 знаков = ИНН физ лица/ИП, 11 знаков в формате 111-222-333 44 - СНИЛС и т.д. Автор - ArkaIIIa
Дата добавления - 28.07.2014 в 14:31
Проверка только по количеству вводимых цифр не совсем то что нужно, точнее ни ко всем кодам применима.
Такие коды как ИНН, СНИЛС, РНН имеют контрольные числа. Необходимо проверить введенное значение и на корректность контрольной суммы.
Пример прилагаю. Там-же есть ссылки на некоторые коды, в которых описана логика проверки контрольных сумм.
Проверка только по количеству вводимых цифр не совсем то что нужно, точнее ни ко всем кодам применима.
Такие коды как ИНН, СНИЛС, РНН имеют контрольные числа. Необходимо проверить введенное значение и на корректность контрольной суммы.
Пример прилагаю. Там-же есть ссылки на некоторые коды, в которых описана логика проверки контрольных сумм. Coolibin
Пример прилагаю. Там-же есть ссылки на некоторые коды, в которых описана логика проверки контрольных сумм. Автор - Coolibin
Дата добавления - 28.07.2014 в 17:59
интересный топик. Но там задача другая. Формировать контрольные суммы для СНИЛС. (и то не по формату)
А у меня стоит задача проверять введенное значение на корректность формата СНИЛС (количество цифр + контрольная сумма)
интересный топик. Но там задача другая. Формировать контрольные суммы для СНИЛС. (и то не по формату)
А у меня стоит задача проверять введенное значение на корректность формата СНИЛС (количество цифр + контрольная сумма) Coolibin
интересный топик. Но там задача другая. Формировать контрольные суммы для СНИЛС. (и то не по формату)
А у меня стоит задача проверять введенное значение на корректность формата СНИЛС (количество цифр + контрольная сумма) Автор - Coolibin
Дата добавления - 29.07.2014 в 17:45
Coolibin
А Вы потрудитесь немного, опишите по каким алгоритмам должна считаться контрольная сумма для СНИЛСа, для ИНН и т.д., а не просто ссылки на Википедию надергайте.
Для проверки контрольных цифр в ИНН юр лиц, уважаемый Russel уже помог составить красивую и лаконичную формулу.
Адаптировать её под 12-тизначные ИНН, а так же под СНИЛС - не проблема. Проверить на кол-во цифр - вообще ерунда. Но не хочется лазить по разным ссылкам, изучая вопрос, который в общем то нужен Вам в первую очередь.
Так что - набросайте файлик, чтобы удобнее было Вам помогать.
Coolibin
А Вы потрудитесь немного, опишите по каким алгоритмам должна считаться контрольная сумма для СНИЛСа, для ИНН и т.д., а не просто ссылки на Википедию надергайте.
Для проверки контрольных цифр в ИНН юр лиц, уважаемый Russel уже помог составить красивую и лаконичную формулу.
Адаптировать её под 12-тизначные ИНН, а так же под СНИЛС - не проблема. Проверить на кол-во цифр - вообще ерунда. Но не хочется лазить по разным ссылкам, изучая вопрос, который в общем то нужен Вам в первую очередь.
Так что - набросайте файлик, чтобы удобнее было Вам помогать. ArkaIIIa
ИНН – идентификационный номер налогоплательщика – присваивается физическим и юридическим лицам. Такие обозначения есть у граждан России, Украины и Беларуси (УНП, ИН). Аналоги существуют у граждан многих других государств.
В данном коде «спрятана» информация о дате рождения, поле человека, коде налоговой (для российских номеров).
Алгоритм расшифровки ИНН в Excel
Идентификационный номер налогоплательщика – это 10 (для юридических) или 12 (для физических лиц) цифр, которые содержат следующую информацию (слева направо):
- код налоговой инспекции, присвоившей ИНН (4 цифры);
- порядковый номер записи в едином государственном реестре налогоплательщиков территориального налогового органа (5 цифр – для организаций, 6 – для физических лиц);
- контрольное число, полученное в ходе применения специального алгоритма (для юридических лиц – 1 цифра, для физических – 2).
Алгоритм проверки ИНН предложен в сервисе «Консультант».
Для десятизначного цифрового кода:
- Найти контрольную сумму с весовыми коэффициентами 2, 4, 10, 3, 5, 9, 4, 6, 8, 0.
- Вычислить частное контрольной суммы и 11. Остаток от деления – контрольное число.
- Если результат предыдущего действия больше 9, то делим его на 10. Остаток от деления – контрольное число.
- Код налогоплательщика считается правильным, если контрольное число совпадает с десятым знаком идентификационного номера.
Для двенадцатизначного кода:
- Найти сумму первых одиннадцати знаков с весовыми коэффициентами 7, 2, 4, 10, 3, 5, 9, 4, 6, 8, 0.
- Контрольное число – остаток от частного суммы и 11.
- Если результат деления больше 9, то находим остаток от частного контрольного числа и 10. Получаем первое контрольное число.
- Найти сумму 12 чисел с весовыми коэффициентами 3, 7, 2, 4, 10, 3, 5, 9, 4, 6, 8, 0.
- Остаток от частного второй контрольной суммы и 11 – второе контрольное число.
- Если оно больше 9, то находим остаток от деления на 10.
- Первое контрольное число правильного ИНН совпадает с 11 знаком номера, второе – с 12.
Проверку правильности ИНН лучше автоматизировать. Неизвестно, реально существующий контрагент выставляет счет к оплате или мошенник. Данная проблема особенно актуальна на крупных предприятиях.
Есть готовые функции для проверки актуальности идентификационного номера: для 10 знаков и для 12. Проверить правильность указания ИНН можно с помощью данной функции.
Реализуем алгоритм проверки с помощью встроенных функций Excel:
- Список номеров введем в столбец А. Перед каждым номером ставим апостроф:
- В ячейку В1 вводим формулу Копируем ее на весь столбец.
Реальные ИНН – «истина», неправильные – «ложь». Данная формула работает только в отношении десятизначных номеров. Если организация работает с физическими и юридическими лицами, то в базу будут заноситься 10-тизначные и 12-тизначные коды.
Просто копируйте и вставляйте – формула рабочая.
Дата рождения по ИНН (Россия) в Excel
Для физических лиц ИНН включает 12 цифр:
где ХХХХ – данные о налоговой инспекции;
ZZZZZZ – порядковый номер налогоплательщика;
YY – контрольный код.
Налогоплательщик получает порядковый номер, когда становится на учет в ФНС. Привязки к дате рождения нет.
Дата рождения по ИНН (Украина) в Excel
Идентификационный номер налогоплательщиков Украины состоит из 10 цифр. Первые 5 – количество дней, которые прошли с 01.01.1900 до рождения.
Например, человек родился 20.09.1976. Первые пять цифр – 28022. Столько дней до его рождения с 31.12.1899 года.
Чтобы найти дату рождения по первым пяти цифрам 28022, нужно 01.01.1900 + 28022 (дней) = 20.09.1976.
Воспользуемся табличным процессором Excel для извлечения даты рождения из идентификационного кода.
Чтобы найти дату рождения, нужно ввести в соседнюю ячейку пять первых цифр плюс 1. И установить формат «Дата». Сделать это можно с помощью ряда функций. Подойдут формулы:
- =ЗНАЧЕН(ЛЕВСИМВ(ТЕКСТ(A2;0);5))+1
- =ЛЕВСИМВ(A2;5)+1
- =ПСТР(A2;1;5)+1
Все три варианта выдают одинаковые результаты.
Данный алгоритм срабатывает не всегда. Четыре цифры за датой рождения в ИНН – порядковый номер плательщика в налоговой службе. Формируется он по мере поступления данных в базу.
Если в какой-то день рождается слишком много людей, то четырех цифр для образования номера не хватает. В данном случае номер начинается с 7 или 8. Цифра указывает на будущее время – алгоритм определения даты неприменим.
Дата рождения по ИНН (Беларусь) в Excel
Граждане Беларуси при постановке на учет в налоговых органах получают УНП (учетный номер плательщика). Это девятизначный код, где:
- первая цифра обозначает регион (у физ. лиц – буква);
- вторая-восьмая – порядковый номер плательщика;
- девятая – контрольная.
С датой рождения УНП не связан.
Зато по идентификационному (личному) номеру, который есть у каждого в паспорте, можно определить дату рождения.
- первая цифра – пол;
- вторая-седьмая – день, месяц и год;
- буква – код региона;
- три цифры за буквой – порядковый номер лица, обратившегося за паспортом;
- две буквы – признак гражданства;
- последняя цифра – контрольная.
Чтобы быстро извлечь дату из личного номера, можно использовать функцию =ПСТР(A2;2;6).
С идентификационными номерами приходится работать бухгалтерам, инженерам по кадрам и другим специалистам. В некоторых организациях такая информация представлена тысячами значений. Обрабатывать вручную и принимать в базу такие коды бесконтрольно – нехорошо. Ряд процессов можно автоматизировать простейшими средствами Microsoft Excel.
Добрый день! Есть макрос для проверки контрагента с сайта Федеральной налоговой службы. Подскажите как его можно модернизировать для того чтобы дополнительно добавлялись 3 столбца - наименование (с сайта ФНС), инн контрагента (с сайта ФНС), кпп контрагента (с сайта ФНС)
Добрый день! Есть макрос для проверки контрагента с сайта Федеральной налоговой службы. Подскажите как его можно модернизировать для того чтобы дополнительно добавлялись 3 столбца - наименование (с сайта ФНС), инн контрагента (с сайта ФНС), кпп контрагента (с сайта ФНС) Flatcher
Про какое наименование вы спрашиваете? Сейчас макрос отправляет запрос на сайт ФНС и получает цифровой код, расшифровка идет в макросе. ИНН и КПП вы сами вводите в запросе, т.е. они уже у Вас есть.
Про какое наименование вы спрашиваете? Сейчас макрос отправляет запрос на сайт ФНС и получает цифровой код, расшифровка идет в макросе. ИНН и КПП вы сами вводите в запросе, т.е. они уже у Вас есть. Udik
Покажите в каком макросе это делается.
Я вижу в модуле 1 два "макроса" - процедуру CheckPartners() и функцию GetContrInfo(dDate As Date, sINN As String, sKPP As String)
Если вы хоть немного понимаете в vba, то сразу увидите, что функция получает ИНН и КПП при вызове. Вызывается она из процедуры, в процедуре последовательно считывается 98 ячеек листа
[vba]
For li = 2 To 100 Step 1
' Передаём параметры в UDF GetContrInfo
Cells(li, 4) = GetContrInfo(Cells(li, 3).Value, Cells(li, 1).Value, Cells(li, 2).Value)
' Прогоняем статус (код) для получения статус (текст)
tmp = Cells(li, 4).Value
Покажите в каком макросе это делается.
Я вижу в модуле 1 два "макроса" - процедуру CheckPartners() и функцию GetContrInfo(dDate As Date, sINN As String, sKPP As String)
Если вы хоть немного понимаете в vba, то сразу увидите, что функция получает ИНН и КПП при вызове. Вызывается она из процедуры, в процедуре последовательно считывается 98 ячеек листа
[vba]
For li = 2 To 100 Step 1
' Передаём параметры в UDF GetContrInfo
Cells(li, 4) = GetContrInfo(Cells(li, 3).Value, Cells(li, 1).Value, Cells(li, 2).Value)
' Прогоняем статус (код) для получения статус (текст)
tmp = Cells(li, 4).Value
For li = 2 To 100 Step 1
' Передаём параметры в UDF GetContrInfo
Cells(li, 4) = GetContrInfo(Cells(li, 3).Value, Cells(li, 1).Value, Cells(li, 2).Value)
' Прогоняем статус (код) для получения статус (текст)
tmp = Cells(li, 4).Value
Flatcher, Вы хотя бы ссылку на страницу ФНС дайте, где можно получить по ИНН или другому признаку наименование и КПП контрагента.
Текущий запрос в макросе вашего файла отправляет ИНН, КПП и дату и в ответ получает лишь код с информацией о регистрации налогоплательщика в ЕГРН. Нет там ни наименования, ни ИНН ни КПП.
И зачем Вам возвращать отправленные Вами же в запросе данные (ИНН и КПП) не понятно.
Если бы в запросе было наименование контрагента, а в ответ приходила инф-я помимо регистрации в ЕГРН еще и его КПП и ИНН, тогда в этом был бы смысл.
Flatcher, Вы хотя бы ссылку на страницу ФНС дайте, где можно получить по ИНН или другому признаку наименование и КПП контрагента.
Текущий запрос в макросе вашего файла отправляет ИНН, КПП и дату и в ответ получает лишь код с информацией о регистрации налогоплательщика в ЕГРН. Нет там ни наименования, ни ИНН ни КПП.
И зачем Вам возвращать отправленные Вами же в запросе данные (ИНН и КПП) не понятно.
Если бы в запросе было наименование контрагента, а в ответ приходила инф-я помимо регистрации в ЕГРН еще и его КПП и ИНН, тогда в этом был бы смысл. Vemod
Office 365 ProPlus переименован в Майкрософт 365 корпоративные приложения. Для получения дополнительной информации об этом изменении прочитайте этот блог.
Симптомы
После ввода длинного номера (например, номера кредитной карты) в ячейке Excel номер отображается неправильно в Excel. Например,
Числовой формат по умолчанию в Excel является общим, поэтому вы можете отображать до 11 цифр в ячейке.
Обходной путь
Для решения проблемы используйте один из указанных ниже способов.
Способ 1. Форматирование ячейки как текста
Для этого выполните следующие действия:
Щелкните правой кнопкой мыши поле со списком и выберите пункт Формат ячеек.
На вкладке Число выберите Текст, а затем нажмите ОК.
Затем введите длинное число. (Не забудьте установить формат ячейки, прежде чем ввести число)
Если вы не хотите видеть предупреждающие стрелки, нажмите на маленькую стрелку, а затем Пропустить ошибку.
Способ 2. Использование одинарной кавычки
При вводе длинного числа сначала введите одинарную кавычку (') в ячейке, а затем длинное число.
Например, введите '1234567890123456789, и одинарная кавычка не будет отображаться после нажатия ENTER.
Связанные статьи
Ваше мнение важно для нас! Не стесняйтесь сообщать, что вы думаете об этой статье, используя поле комментария, расположенное внизу документа. Это позволит нам улучшить содержимое. Заранее спасибо!
Читайте также: