Сохранение начальных нулей и больших чисел
Вам когда-нибудь приходилось импортировать или вводить в Excel данные, содержащие начальные нули (например, 00123) или большие числа (например, 1234 5678 9087 6543)? Это могут быть номера социального страхования, телефонные номера, номера кредитных карт, коды продуктов, номера счетов или почтовые индексы. Excel автоматически удаляет начальные нули и преобразует большие числа в экспоненциальное представление (например, 1,23E+15), чтобы их можно было использовать в формулах и математических операциях. В этой статье объясняется, как сохранить данные в исходном формате, который Excel обрабатывает как текст.
Преобразование чисел в текст при импорте текстовых данных
Для форматирования отдельных столбцов в виде текста при импорте данных в Excel используйте функцию Получить и преобразовать (Power Query). В этом случае импортируется текстовый файл, однако данные проходят те же этапы преобразования, что и при импорте из других источников, таких как XML, Интернет, JSON и т. д.
- Откройте вкладку Данные, нажмите кнопку Получить данные и выберите вариант Из текстового/CSV-файла. Если вы не видите кнопку Получить данные, выберите Создать запрос >Из файла >Из текста, найдите нужный файл и нажмите кнопку Импорт.
- Excel загрузит данные в область предварительного просмотра. В области предварительного просмотра нажмите кнопку Изменить, чтобы загрузить Редактор запросов.
- Если какие-либо столбцы нужно преобразовать в текст, выделите их, щелкнув заголовок, затем выберите Главная >Преобразовать >Тип данных >Текст.
Совет: Чтобы выбрать несколько столбцов, щелкните их левой кнопкой мыши, удерживая нажатой клавишу CTRL.
В Excel 2010 и Excel 2013 импортировать текстовые файлы и преобразовывать числа в текст можно двумя способами. Рекомендуется использовать Power Query (для этого нужно скачать надстройку Power Query). Если надстройку Power Query скачать не удается, можно воспользоваться мастером импорта текста. В этом случае импортируется текстовый файл, однако данные проходят те же этапы преобразования, что и при импорте из других источников, таких как XML, Интернет, JSON и т. д.
- На ленте откройте вкладку Power Query и выберите Получение внешних данных >Из текста.
- Excel загрузит данные в область предварительного просмотра. В области предварительного просмотра нажмите кнопку Изменить, чтобы загрузить Редактор запросов.
- Если какие-либо столбцы нужно преобразовать в текст, выделите их, щелкнув заголовок, затем выберите Главная >Преобразовать >Тип данных >Текст.
Совет: Чтобы выбрать несколько столбцов, щелкните их левой кнопкой мыши, удерживая нажатой клавишу CTRL.
Применение пользовательского формата для сохранения начальных нулей
Если эту задачу необходимо решить в пределах книги, поскольку она не используется другими программами в качестве источника данных, для сохранения начальных нулей можно использовать пользовательский или специальный формат. Этот способ подходит для цифровых кодов, содержащих менее 16 цифр. Цифровые коды также можно отформатировать с использованием дефисов или других знаков препинания. Например, чтобы сделать телефонные номера более удобными для чтения, можно добавить дефисы между международным кодом, кодом области, префиксом и остальными цифрами.
Цифровой код
Пользовательский числовой формат
Социальные сети
Безопасности
Как поставить или вставить «0» нули перед значением или последовательностью цифр в таблице Microsoft Excel
Чтобы добавить ноль перед числом или значением. Откройте документ с исходными данными в программе Microsoft Office Excel. Для того чтобы корректно отображались цифры, и перед цифрами можно было поставить ноль 0, надо выставить формат ячеек в текстовой вид. Для этого выделите требуемые ячейки, нажмите правой кнопкой выбрать «формат ячеек — число — числовой формат — текстовый»
Второй способ:
можно поставить знак апострофа → » ‘ » впереди числовой последовательности, и формат ячейки автоматически перейдет в текстовой.
Если Вам уже надо перевести готовый массив чисел в текстовой формат ячеек, числа выглядят в формате вида «1,234567E+16», нужно просто каждую ячейку прокликать два раза и навести на свободную область и также кликнуть. 
Есть вопросы, оставляйте комментарии!
(Эксель) Microsoft Office. Excel 2003, 2007, 2010, 2013, 2016, 2019, 365
Как сделать так чтобы 0 впереди не убирался в excel
Подскажите пожалуйста, как сделать, чтобы если данные из одной ячейки дублируются в другую ячеек через =адрес ячейки, чтобы при отсутствии данных в связанной ячейке не появлялся бы ноль (0).
Чтобы, если удаляешь данные из ячейки A1, то в ячейке A2 было бы пусто, без ноля.
Прикрепленные файлы
- Пример №1.xlsx (8.12 КБ)
Пользователь
Сообщений: 9709 Регистрация: 22.12.2012
03.06.2015 22:08:29
совсем пусто — это только макросом.
а чтобы нуля не было — можно так:
=ЕСЛИ(ЕПУСТО(A1);"";A1)
фрилансер Excel, VBA — контакты в профиле
«Совершенствоваться не обязательно. Выживание — дело добровольное.» Э.Деминг
Пользователь
Сообщений: 4093 Регистрация: 01.01.1970
03.06.2015 22:14:25
=ЕСЛИ(A1;A1;"")
но только если эта ячейка не участвует в расчетах. Или все нули можно не отображать на листе, если в Параметрах — Дополнительно — Показывать нули в ячейках, которые содержат нулевые значения, убрать галочку
Изменено: gling — 03.06.2015 22:20:38
Пользователь
Сообщений: 403 Регистрация: 18.12.2013
03.06.2015 22:20:34
В ячейке A2 уже есть одна формула =A1. Скажите, а может быть в ячейке две формулы?
Изменено: Olga H. — 03.06.2015 22:24:39
Сообщений: 60949 Регистрация: 14.09.2012
Контакты см. в профиле
03.06.2015 22:24:43
Отображение нуля в ячейках ещё можно отключить в параметрах Excel.
Пользователь
Сообщений: 4093 Регистрация: 01.01.1970
03.06.2015 22:25:06
| Цитата |
|---|
| может быть в ячейке две формулы? |
А зачем? Написанные выше формулы заменяют формулу =A1.
Пользователь
Сообщений: 403 Регистрация: 18.12.2013
03.06.2015 22:32:15
gling , спасибо! Всё получилось без формулы. Параметры Excel — Дополнительно — сняла галочку с Показывать нули в ячейках, которые содержат нулевые значения-ok-и нули пропали. Документ, в котором эту функцию использую, не связан с расчетами.
Сообщений: 60949 Регистрация: 14.09.2012
Контакты см. в профиле
03.06.2015 22:35:35
Ну нормально — я про параметры сказал, а спасиба ушла gling
Пользователь
Сообщений: 108 Регистрация: 01.01.1970
03.06.2015 23:14:06
Формат ячеек > Все форматы> 0;;
Пользователь
Сообщений: 403 Регистрация: 18.12.2013
03.06.2015 23:38:48
Во «Все форматы» не нашла > 0;; Может это надо самой вписать в окно Тип?
Сообщений: 60949 Регистрация: 14.09.2012
Контакты см. в профиле
03.06.2015 23:44:25
Да, вручную.
Пользователь
Сообщений: 14615 Регистрация: 15.09.2012
Профессиональная разработка приложений для MS Office
03.06.2015 23:45:36
| Цитата |
|---|
| Юрий М написал: Ну нормально — я про параметры сказал, а спасиба ушла gling |
Так он раньше тебя про параметры сказал. Кто первый встал — того и тапки 🙂
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы.
Сообщений: 60949 Регистрация: 14.09.2012
Контакты см. в профиле
03.06.2015 23:47:19
Всё равно обидно)
Пользователь
Сообщений: 403 Регистрация: 18.12.2013
04.06.2015 00:00:40
magrifa , ваш вариант более всего подошёл, потому что убирает ноли с выделенной зоны, а не со всего листа.
Выделяю ячейки, в которых не хочу видеть нули-Формат ячеек- Все форматы-Из поля Тип удаляю Основные-вставляю в поле Тип > 0;; -жму ОК- из выделенных ячеек нули исчезают, в остальных ячейках- остаются
Пользователь
Сообщений: 14615 Регистрация: 15.09.2012
Профессиональная разработка приложений для MS Office
04.06.2015 11:30:11
Вообще правильно формат должен выглядеть так: 0;-0;
Почему? Проверьте, записав в ячейку отрицательное число. С форматом 0;; оно так же будет невидимо в ячейке, как и нули. С форматом 0;-0; — будут отображаться любые числа и текст, но не нули.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы.
Как сделать так чтобы 0 впереди не убирался в excel
Аналогичная проблема для кодов была когда-то решена (третий столбец получен из первого), но не помню алгоритма решения.
Прикрепленные файлы
- номенклатура_для вопроса.xlsx (8.93 КБ)
Изменено: alfastudent — 25.07.2016 13:15:09
Пользователь
Сообщений: 23775 Регистрация: 22.12.2012
25.07.2016 12:07:34
Поставьте ячейкам текстовый формат.
Или используйте custom format ячейки.
Ну или перед числом апостроф: ‘0088
Смотря что нужно по задаче.
Изменено: Hugo — 25.07.2016 12:08:41
Пользователь
Сообщений: 14 Регистрация: 17.05.2015
25.07.2016 13:12:31
По задаче нужно — сохранить указанные значения с нулями в текстовом формате.
Простановка текстового формата автоматически убирает нули.
Апостроф не ставится добавлением «‘»&B2; даже при редактировании вручную (нужно для 50 000 строк) в строке со значением сразу стоит число без нулей. Если только вручную дописывать ’00 к 88.
Соответственно, нужны другие методы решения.
Пользователь
Сообщений: 14 Регистрация: 17.05.2015
25.07.2016 13:13:07
Прошу прощения, видимо не приложил файл сразу.
Изменено: alfastudent — 25.07.2016 13:15:28
Пользователь
Сообщений: 684 Регистрация: 22.12.2012
25.07.2016 13:16:08
| Цитата |
|---|
| alfastudent написал: Прошу прощения, видимо |
Так просите или нет? Определитесь уже.
Покажите файл с этими «нулями».
Пользователь
Сообщений: 23775 Регистрация: 22.12.2012
25.07.2016 13:16:53
| Цитата |
|---|
| alfastudent написал: По задаче нужно — сохранить указанные значения с нулями в текстовом формате. |
какие значения? Те которые уже 88? Или они где-то есть как 0088, и их нужно занести на лист?
В общем пока точно и подробно не ясна задача (что-где-как-куда-зачем-почему) практически решать нечего.
Пользователь
Сообщений: 23775 Регистрация: 22.12.2012
25.07.2016 13:17:49
| Цитата |
|---|
| alfastudent написал: Простановка текстового формата автоматически убирает нули. |
никак нет, ничего это действие и не убирает, и не добавляет.
Пользователь
Сообщений: 14 Регистрация: 17.05.2015
25.07.2016 13:25:04
Они видны как 0088. При этом имеется только этот файл.
Нужно, чтобы ОТОБРАЖАЕМЫЕ коды типа 0088 были ЗАПИСАНЫ в столбец D с нулями, несмотря на то, что значения в B сразу без нулей.
Пользователь
Сообщений: 23775 Регистрация: 22.12.2012
25.07.2016 13:28:27
| Цитата |
|---|
| alfastudent написал: Нужно, чтобы ОТОБРАЖАЕМЫЕ коды типа 0088 были ЗАПИСАНЫ в столбец D с нулями |
я уже говорил про формат ячейки? Про пользовательский формат.
Но можно сделать формулами или макросом, но тоже нужно задействовать формат ячейки. Текстовый.
P.S. наконец появился файл — но толку от него немного, куда и сколько нулей нужно не рассказали.
Изменено: Hugo — 25.07.2016 13:30:57
Пользователь
Сообщений: 14 Регистрация: 17.05.2015
25.07.2016 13:34:48
Custom format — не совсем понял, какой именно формат в данном случае сделать для данной задачи. Не вижу общего формата для данных чисел, к которому следовало бы привести их.
PS Нулей должно быть столько же, сколько отображается в столбце В. Для каждой строки — по своему. Может быть например число 00008, которое должно быть сохранено в D именно как текст «00008».
Изменено: alfastudent — 25.07.2016 13:37:11
Пользователь
Сообщений: 23775 Регистрация: 22.12.2012
25.07.2016 13:41:40
| Цитата |
|---|
| alfastudent написал: Нулей должно быть столько же, сколько отображается в столбце В. |
там вообще ничего не отображается! Поэтому не могу сказать про сколько нулей. Но если нужно всего 6 символов с ведущими нулями — то формат 000000
Но если нужно «которое должно быть сохранено в D именно как текст «00008»» — то тут уже формат не поможет, он не изменяет то, что «должно быть сохранено».
Вы уж разберитесь и нас просветите — должно отображаться , или должно быть в ячейке? Это разные вещи.
Изменено: Hugo — 25.07.2016 13:43:56
Пользователь
Сообщений: 8196 Регистрация: 21.12.2012
25.07.2016 13:47:11
alfastudent, расскажите:
1.где изначально находятся артикулы с нулями (с видимыми нулями)
2. что Вы делаете, когда они пропадают (копируете, экспортируете или..)
«..Сладку ягоду рвали вместе, горьку ягоду я одна.»
Пользователь
Сообщений: 14 Регистрация: 17.05.2015
25.07.2016 13:52:08
В приложенном мной файле отображаются числа с нулями, как в приложенном скриншоте. При этом в строке значений видно именно значение без нулей.
В скачанном Вами файле действительно «вообще ничего не отображается», и нули в столбце В вначале всё же отображаются в колонке?
Прикрепленные файлы
- 0088.png (5.9 КБ)
Пользователь
Сообщений: 23775 Регистрация: 22.12.2012
25.07.2016 13:56:25
| Цитата |
|---|
| alfastudent написал: и нули в столбце В вначале всё же отображаются |
в этом столбце только заголовок! На Вашем скриншоте правда этого столбца нет, так что не могу сравнить.
А в F нули есть, выводятся форматом ячейки — только не изучал каим именно.