Сохранение начальных нулей и больших чисел
Вам когда-нибудь приходилось импортировать или вводить в 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 цифр. Цифровые коды также можно отформатировать с использованием дефисов или других знаков препинания. Например, чтобы сделать телефонные номера более удобными для чтения, можно добавить дефисы между международным кодом, кодом области, префиксом и остальными цифрами.
Цифровой код
Пользовательский числовой формат
Социальные сети
Безопасности
покупка
Как правило, когда мы вводим в ячейку число, которое содержит ведущие нули, например 00032423, оно может отображаться как 32423. И как сохранить эти ведущие нули в Excel? И наоборот, как удалить ведущие нули, если их не нужно показывать?
Удалить начальные нули с помощью Kutools for Excel по кликам
Сохраняйте ведущие нули с помощью форматных ячеек
Если вы хотите сохранить начальные нули в Excel, вам просто нужно отформатировать ячейки как текстовое форматирование перед вводом данных.
1. Выберите ячейки диапазона, в которые вы хотите ввести данные с ведущими нулями, и щелкните правой кнопкой мыши, чтобы выбрать Формат ячеек из контекстного меню. Смотрите скриншот:
2. в Формат ячеек диалоговое окно, нажмите Число Вкладка и выберите Текст из Категории панель. Нажмите OK чтобы закрыть диалог. Смотрите скриншот:
Теперь, когда вы вводите числа с ведущими нулями в ячейки диапазона, ведущие нули будут сохраняться в ячейках. Смотрите скриншот:
Внимание: Этот метод не может работать, если вы форматируете ячейки как текст после ввода чисел.
Один щелчок, чтобы удалить все ведущие нули из ячеек в Excel
Удалите ведущие нули с помощью VBA
Если вы хотите удалить ведущие нули в Excel, у вас есть четыре способа решить эту проблему.
Метод 1 Форматирование ячеек как числовое форматирование
Выберите диапазон, в котором вы хотите ввести число без отображения начальных нулей, и щелкните правой кнопкой мыши, чтобы щелкнуть Формат ячеек , чтобы открыть Формат ячеек диалоговое окно и выберите Число из Категории панель, затем щелкните OK.
Внимание: Этот метод не может работать, если вы форматируете ячейки после ввода числа.
Метод 2 Используйте формулу
Выберите пустую ячейку рядом с ячейками диапазона, в которые вы ввели числа с ведущими нулями, и введите эту формулу = ЗНАЧЕНИЕ (A1) (A1 указывает, что ячейка содержит числа с ведущими нулями). И нажмите Enter , теперь в ячейке формулы отображается число без нуля в начале. Смотрите скриншот:
Метод 3: запуск VBA
1. Нажмите Alt + F11 для отображения Microsoft Visual Basic для приложений окно.
2. В окне нажмите Вставить > Модули чтобы отобразить новое окно модуля, затем скопируйте следующий код VBA в окно модуля.
VBA: удалить ведущие нули в Excel
Sub DeleteZero() 'updateby20140616 Dim Rng As Range Dim WorkRng As Range On Error Resume Next xTitleId = "KutoolsforExcel" Set WorkRng = Application.Selection Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8) WorkRng.NumberFormat = "General" WorkRng.Value = WorkRng.Value End Sub 3. Нажмите Run Кнопка, и появится диалоговое окно KutoolsforExcel, в котором вы можете выбрать диапазон для удаления ведущих нулей. Смотрите скриншот:
4. Нажмите OK. Затем удаляются ведущие нули в выделении.
Метод 4: использование функции преобразования в число
Выделите ячейки диапазона, заполненные числами с ведущими нулями, щелкните значок предупреждения рядом с выделенным, а затем выберите Преобразовать в число из контекстного меню. См. Снимок экрана:
Удалить начальные нули с помощью Kutools for Excel по кликам
После установки Kutools for Excel, пожалуйста, сделайте следующее: (Бесплатная загрузка Kutools for Excel Сейчас!)
Если у вас есть Kutools for Excel, вы можете быстро удалить все ведущие нули из текстов двумя щелчками мыши по его Удалить ведущие нули утилита
Выделите текст, в котором нужно удалить ведущие нули, и нажмите Кутулс > Текст > Удалить ведущие нули. Смотрите скриншот:
Затем появляется диалоговое окно, напоминающее, что из числа ячеек были удалены ведущие нули. Смотрите скриншот:
Нажмите OK для закрытия диалога.
Удалить ведущие нули
Добавить/удалить ведущие нули с помощью Kutools for Excel
Если вы хотите добавить или удалить такое же количество ведущих нулей в ячейке, вы можете применить Kutools for Excel‘s Добавить текст и Удалить по позиции Утилиты, один может помочь вам добавить такое же количество ведущих нулей в ячейки, один поможет вам удалить такое же количество строк слева от ячеек.
После установки Kutools for Excel, пожалуйста, сделайте следующее: (Бесплатная загрузка Kutools for Excel Сейчас!)
Добавьте такое же количество ведущих нулей
1. Во-первых, вам нужно отформатировать ячейки как Текст выбрав их и нажав Главная и перейти в Числовой группа для выбора Текст из раскрывающегося списка. Смотрите скриншот:
2. Затем, не снимая выделения с этих ячеек, щелкните Кутулс > Текст > Добавить текст. Смотрите скриншот:
3. в Добавить текст диалоговом окне введите нули, которые вы хотите добавить, в текстовое поле Текст, и проверьте Перед первым персонажем вариант, и вы можете предварительно просмотреть результат добавления на правой панели. Смотрите скриншот:
4. Нажмите Ok or Применить, теперь в ячейки добавляется такое же количество ведущих нулей.
Удалить одинаковое количество ведущих нулей
1. Выберите ячейки, в которых нужно удалить ведущие нули, и нажмите Кутулс > Текст > Удалить по позиции. Смотрите скриншот:
2. В появившемся диалоговом окне введите количество нулей, которые вы хотите удалить, установите флажок, и вы сможете предварительно просмотреть результат на панели предварительного просмотра.
3. Нажмите Ok or Применить, теперь начальные нули удаляются сразу.
Просмотр и редактирование нескольких книг Excel / документов Word с вкладками в Firefox, Chrome, Internet Explore 10!
Возможно, вы привыкли просматривать несколько веб-страниц в Firefox/Chrome/IE и легко переключаться между ними, щелкая соответствующие вкладки. Здесь, Office Tab поддерживает аналогичную обработку, которая позволяет просматривать несколько книг Excel или документов Word в одном окне Excel или окне Word и легко переключаться между ними, щелкая их вкладки. Нажмите бесплатно 30 -дневная пробная версия Office Tab!
Относительные статьи:
- Удалить выпадающий список в Excel
- Удалить одну или все сводные таблицы в Excel
Как сделать чтобы эксель не убирал ноль в начале
Зачастую, при организации ввода новых данных в таблицу EXCEL, например новых артикулов, заказов, необходимо соблюдать определенное к-во цифр (разрядов) в значении, а пустые незаполненные разряды перед числом просто заполнять нулями.
В нашем примере, новый заказ 256 необходимо отображать как 000256, т.к. изначально планируется, что к-во заказов будет представлять из себя шестизначное число.
Для того, чтобы задать такой формат, необходимо:
-
Кликнуть правой клавишей на столбце или диапазоне ячеек, в котором мы хотим настроить требуемое отображение разрядов чисел и выбрать «Формат ячеек».
В открывшемся окне перейти на группу «(все форматы)» и выбрать значение «0» в списке типов.
Справа в столбце автоматически формируется Код заявки, содержащий идентификатор источника заявки. Как он формируется, поговорим в следующих публикациях.
Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:
(для перевода по карте нажмите на VISA и далее «перевести»)
Как сделать чтобы эксель не убирал ноль в начале
Аналогичная проблема для кодов была когда-то решена (третий столбец получен из первого), но не помню алгоритма решения.
Прикрепленные файлы
- номенклатура_для вопроса.xlsx (8.93 КБ)
Изменено: alfastudent — 25.07.2016 13:15:09
Пользователь
Сообщений: 23778 Регистрация: 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 написал: Прошу прощения, видимо |
Так просите или нет? Определитесь уже.
Покажите файл с этими «нулями».
Пользователь
Сообщений: 23778 Регистрация: 22.12.2012
25.07.2016 13:16:53
| Цитата |
|---|
| alfastudent написал: По задаче нужно — сохранить указанные значения с нулями в текстовом формате. |
какие значения? Те которые уже 88? Или они где-то есть как 0088, и их нужно занести на лист?
В общем пока точно и подробно не ясна задача (что-где-как-куда-зачем-почему) практически решать нечего.
Пользователь
Сообщений: 23778 Регистрация: 22.12.2012
25.07.2016 13:17:49
| Цитата |
|---|
| alfastudent написал: Простановка текстового формата автоматически убирает нули. |
никак нет, ничего это действие и не убирает, и не добавляет.
Пользователь
Сообщений: 14 Регистрация: 17.05.2015
25.07.2016 13:25:04
Они видны как 0088. При этом имеется только этот файл.
Нужно, чтобы ОТОБРАЖАЕМЫЕ коды типа 0088 были ЗАПИСАНЫ в столбец D с нулями, несмотря на то, что значения в B сразу без нулей.
Пользователь
Сообщений: 23778 Регистрация: 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
Пользователь
Сообщений: 23778 Регистрация: 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 КБ)
Пользователь
Сообщений: 23778 Регистрация: 22.12.2012
25.07.2016 13:56:25
| Цитата |
|---|
| alfastudent написал: и нули в столбце В вначале всё же отображаются |
в этом столбце только заголовок! На Вашем скриншоте правда этого столбца нет, так что не могу сравнить.
А в F нули есть, выводятся форматом ячейки — только не изучал каим именно.