Содержание
- Способ 1: Изменение формата ячейки на «Текстовый»
- Способ 2: Создание своего формата ячеек
- Способ 3: Быстрое изменение формата ячейки на текстовый
- Способ 4: Форматирование чисел в новых ячейках
- Вопросы и ответы
Способ 1: Изменение формата ячейки на «Текстовый»
Самое простое действие, которое можно предпринять при необходимости добавить к числам нули, — изменить формат необходимых ячеек на текстовый, чтобы больше не испытывать проблем с автоматическим удалением ненужных нулей. Для этой настройки в Excel есть специально отведенное меню.
- Сразу выделите все требуемые ячейки с числами, зажав левую кнопку мыши.
- На вкладке «Главная» откройте раздел «Ячейки».
- Вызовите выпадающее меню «Формат».
- В нем кликните по последнему пункту «Формат ячеек».
- Появится новое окно настройки формата, где в левом блоке дважды щелкните «Текстовый», чтобы применить этот тип. Если окно не закрылось автоматически, сделайте это самостоятельно.
- Вернитесь к значениям в ячейках и добавьте нули там, где это нужно. Учитывайте, что при такой настройке сумма их теперь считаться не будет, поскольку формат ячеек не числовой.
Способ 2: Создание своего формата ячеек
Более совершенный метод, который подойдет для автоматической установки нулей перед числами тогда, когда вручную это делать не хочется или же процедура займет много времени. Создание своего формата ячеек возможно в том случае, если изначально вы знаете, как должны располагаться цифры, например при коде страхования или любом идентификаторе. За пример возьмем тип 000 000 000.
- Выделите все настраиваемые блоки с числами, откройте то же выпадающее меню для настройки ячеек и перейдите в «Формат ячеек».
- На этот раз выберите «(все форматы)».
- Не используйте таблицу с типами, а вручную заполните его так, чтобы образец обрел правильный вид.
- Вернитесь к таблице и убедитесь в том, что все настройки успешно применены.
Способ 3: Быстрое изменение формата ячейки на текстовый
При редактировании конкретной ячейки можно использовать синтаксис Excel, чтобы обозначить текст. Это пригодится в текущей ситуации для быстрого изменения типа значения и добавления нулей.
- В этом случае выделите ячейку и активируйте ее поле для изменения.
- Поставьте знак «‘» в начале без пробела.
- После этого знака добавьте необходимое количество нулей, используя пробелы, если это потребуется. Нажмите Enter для подтверждения изменений.
- Убедитесь в том, что теперь содержимое в ячейке отображается корректно.
Способ 4: Форматирование чисел в новых ячейках
Последний вариант добавления нулей перед числами в Excel подразумевает форматирование содержимого ячейки в новом блоке при помощи функции ТЕКСТ. Учитывайте, что в этом случае создаются новые ячейки с данными, что и будет показано ниже.
- Для начала определимся с форматом чисел. Вы можете использовать пробелы или дефис, что зависит от необходимого типа записи.
- В пустой клетке начните записывать формулу «=ТЕКСТ».
- После добавления открывающихся и закрывающихся скобок укажите ячейку для форматирования.
- Добавьте точку с запятой, чтобы отделить значение от формата.
- Откройте двойные кавычки и напишите, в каком типе должен показываться текст (о его подборе мы уже говорили выше).
- Нажмите по клавише Enter и посмотрите на результат. Как видно, значение подогнано под количество символов, а недостающие числа заменяются нулями впереди, что и требуется при составлении списков идентификаторов, номеров страхования и других сведений.
В качестве дополнительной информации рекомендуем ознакомиться с двумя другими вспомогательными статьями на нашем сайте, которые пригодятся при форматировании содержимого ячеек и использовании формул.
Читайте также:
Удаление нулевых значений в Microsoft Excel
Удаление пробела между цифрами в Microsoft Excel
Еще статьи по данной теме:
Помогла ли Вам статья?
Иногда перед пользователем стоит задача добавить нули перед числом в Excel. Например, табельные номера, как правило, начинаются с нулей. И если вводить с клавиатуры число, начинающееся с нуля, эксель по умолчанию убирает ноль перед числом.
Способ 1. Текстовый формат ячеек
Например, нам нужно внести в таблицу excel табельные номера, начинающиеся с нулей.
- Выделим ячейки, в которые нужно внести данные.
- Нажмем правую кнопку мыши и выберем Формат ячеек
1. На вкладке Число выберем Текстовый формат и нажмем Ок.
2. Теперь можно вносить в эти ячейки числа с любым количеством нулей в начале.
Однако, обратите внимание на зеленый треугольники в верхнем левом углу ячейки. В данном случае это указывает на некоторые проблемы с форматом. Дело в том, что эти числа — не совсем числа. Совершать числовые операции с ними невозможно (например, их нельзя сложить или умножить). Поэтому этот способ можно использовать только в том случае, если с этими числами не требуется делать каких-то операций.
Лайфхак!
Текстовый формат ячейке, чтобы поставить ноль перед числом в excel, можно задать более быстрым способом.
Начинаем ввод в ячейку с одинарной кавычки, далее — нули и необходимые числа. Результат будет такой же — ячейке будет присвоен текстовый формат.
У нас уже внесены числа без нулей, и нужно быстро добавить нули перед числами во всем столбце. Здесь поможет функция ТЕКСТ.
В столбце рядом напишем формулу:
=ТЕКСТ(B3;»000000″)
Функция ТЕКСТ преобразует значение в ячейке, на которую ссылается в указанные формат.
В примере это ячейка B3, значение в которой преобразуется в формат “000000”. Этот формат означает, что, если вы напишите в ячейке B3 число, состоящее меньше, чем из 6 цифр, то excel добавит в начале нужное количество нулей, сделав число шестизначным.
В этой статье мы научились, как можно добавить нули перед числом в Excel.
Интересное по теме:
Сообщество Excel Analytics | обучение Excel
Канал на Яндекс.Дзен
Вам может быть интересно:
Иногда, необходимо сделать так, чтобы нули в начале чисел автоматически не удалялись программой Excel. Например, вы составляете таблицу с артикулами и вам просто необходимо, чтобы числа отображались полностью.
Грубо говоря, нужно чтобы числа прописывались так же, как на картинке ниже.
Итак, я продемонстрирую вам несколько способов, как можно привести числа в такой вид.
У каждого способа, естественно, есть свои плюсы и минусы, подробнее об этом я расскажу в самой статье.
Содержание
- Способ 1: изменяем формат ячейки
- Способ 2: указываем формат отображения чисел
- Способ 3: с помощью функции ТЕКСТ
- Способ 4: изменяем длину строки
- Способ 5: используем Visual Basic
Способ 1: изменяем формат ячейки
у нас есть «ID» товаров магазина, как на картинке ниже.
И нам нужно записывать эти самые «ID», например, как 00001, а Excel автоматически переписывает его в 1.
Дело в том, что Excel пытается привести числа в простой вид. Он сравнивает числа, к примеру, 00002 и 2, понимает что они одинаковые (именно как числа) и преобразовывает 00002 в простой вид 2.
Но нам все равно необходимо записать 00002, тогда мы можем изменить формат ячейки в текстовый.
Пошаговая инструкция:
- Выделите ячейки с числами, которые нужно записать с нулями;
- Щелкните в раздел «Главная» и в формате выберите «Текстовый»;
В принципе все, теперь попробуйте заново записать ваши числа (нужно именно еще раз их прописать. Числа которые Excel уже преобразовал в «простой» вид, обратно не изменятся).
Важная информация: этот способ может не подойти вам, если вы, в будущем, будете использовать эти ячейки в расчетах, потому что ячейка теперь в текстовом формате, а считать возможно только числа.
Способ 2: указываем формат отображения чисел
Этот способ позволит вам, в дальнейшем, использовать записанные числа в расчетах.
В чем суть:
Число вы можете записать по-разному, например, 2000 и 2000.00 это одно и то же, по этой логике, мы можем просто изменить формат отображения числа. То есть придать именно отображению другой вид, при этом формат ячейки останется числовым.
Таким образом содержимое ячейки не изменится.
Пошаговая инструкция:
- Выделите нужные ячейки;
- Щелкните «Главная» и нажмите на стрелочку, смотрящую вниз, в разделе «Число»;
- В открывшемся окне, во вкладке «Число», щелкните на «(все форматы)»;
- В поле «Тип» (где сейчас написано «Основной») напишите «00000»;
- Подтвердите.
Итак, что произошло: мы поменяли вид отображения всех чисел в выделенных ячейках на пятизначный. Это значит, что все числа в этих ячейках будут отображаться, минимум, в пятизначном формате. Например, число «2» будет отображаться как «00002». А число «372847» будет отображаться как «372847».
В общем, тут все зависит от вашей ситуации, нулей в поле «Тип» можно вписать столько, сколько вам нужно. Можно сделать десятизначный формат отображения и так далее.
Важная информация: этот способ работает только с ячейками формата «Число», если у вашей ячейки будет текстовый формат, этот вариант не сработает.
Способ 3: с помощью функции ТЕКСТ
Итак, важно понимать, что функция ТЕКСТ изменяет формат самого значения, а не всей ячейки.
Но в целом, этот способ подходит если вам просто нужно сделать, чтобы число отображалось так, как вы хотите.
Но не забывайте, что использование этой функции превратит значение в текст. Это значит что для Excel ваше число (например, 00002) будет считаться обычным текстом, т.е. в расчетах его использовать будет невозможно.
Формула:
=ТЕКСТ(A2, “00000”)Для числа, хранящегося в ячейке «A2».
Итак, число будет записываться в пятизначном формате(если число было «3», то станет «00003»).
Плюс этого метода заключается в том, что эти числа можно будет искать с помощью функций поиска. Так как они считаются обычным текстом, поиск по тексту будет работать.
Важная информация: этот способ может не подойти вам, если вы, в будущем, хотите использовать эти числа в расчетах, потому что они теперь будут считаться обычным текстом.
Способ 4: изменяем длину строки
Минус способа с использованием функции ТЕКСТ является то, что она не способна изменять формат чисел на текст тогда, когда эти числа находятся в комбинации с текстом. Она просто не будет работать.
Но в такой ситуации можно использовать метод с функциями ПОВТОР и ДЛСТР.
Формула:
=ПОВТОР(0;5-ДЛСТР(A2))&A2При вызове такой функции, длина строки изменится на пятизначную. Это значит, что все числа будут записаны в пятизначном формате (например, число «3» будет записано как «00003»).
Что делает эта функция:
- Функция ДЛСТР(A2) возвращает длину нашей строки с числом.
- Функция =ПОВТОР(0;5-ДЛСТР(A2)) считает сколько нулей необходимо добавить к началу нашего числа. И так для каждой новой строки.
Грубо говоря, эта функция просто посчитает сколько нулей нужно добавить к началу числа чтобы в нем, в общем, получилось 5 знаков (т.к. в формуле мы указали 5).
Способ 5: используем Visual Basic
Способ с использованием Visual Basic отлично подойдет тем, кому очень часто нужно записывать числа определенным образом.
Этот код создает вашу собственную функцию для такого рода задачи:
Function AddLeadingZeroes(ref As Range, Length As Integer)
Dim i As Integer
Dim Result As String
Dim StrLen As Integer
StrLen = Len(ref)
For i = 1 To Length
If i <= StrLen Then
Result = Result & Mid(ref, i, 1)
Else
Result = "0" & Result
End If
Next i
AddLeadingZeroes = Result
End FunctionНужно поместить код, данный выше, в Visual Basic модуль и тогда в вашем Excel добавится функция, которая автоматически будет прописывать числа с нулями.
А еще можно поделиться этой функцией с коллегами.
Отображение и скрытие нулевых значений
Вы можете сами настроить отображение нулевых значений в ячейке или использовать в таблице набор стандартов форматирования, которые требуют скрывать нулевые значения. Отображать и скрывать нулевые значения можно разными способами.
Потребность отображать нулевые значения (0) на листах возникает не всегда. Требуют ли стандарты форматирования или собственные предпочтения отображать или скрывать нулевые значения, имеется несколько способов реализации всех этих требований.
Скрытие и отображение всех нулевых значений на листе
-
Выберите Файл > Параметры > Дополнительно.
-
В группе Показать параметры для следующего листа выберите лист, после чего выполните одно из указанных ниже действий.
-
Чтобы отображать в ячейках нулевые значения (0), установите флажок Показывать нули в ячейках, которые содержат нулевые значения.
-
Чтобы отображать нулевые значения в виде пустых ячеек, снимите флажок Показывать нули в ячейках, которые содержат нулевые значения.
-
Скрытие нулевых значений в выделенных ячейках
Эти действия скрывают нулевые значения в выбранных ячейках с помощью числовых форматов. Скрытые значения отображаются только в панели формул и не печатаются. Если значение в одной из этих ячеек изменится на неосвое число, то значение отобразится в ячейке, а формат значения будет аналогиен общему числовому формату.
-
Выделите ячейки, содержащие нулевые значения (0), которые требуется скрыть.
-
Вы можете нажать клавиши CTRL+1 или на вкладке Главная щелкнуть Формат > Формат ячеек.
-
Щелкните Число > Все форматы.
-
В поле Тип введите выражение 0;-0;;@ и нажмите кнопку ОК.
Отображение скрытых значений.
-
Выделите ячейки со скрытыми нулевыми значениями.
-
Вы можете нажать клавиши CTRL+1 или на вкладке Главная щелкнуть Формат > Формат ячеек.
-
Для применения числового формата, определенного по умолчанию, выберите Число > Общий и нажмите кнопку ОК.
Скрытие нулевых значений, возвращенных формулой
-
Выделите ячейку, содержащую нулевое (0) значение.
-
На вкладке Главная щелкните стрелку рядом с кнопкой Условное форматирование и выберите «Правила выделения ячеек» > «Равно».
-
В левом поле введите 0.
-
В правом поле выберите Пользовательский формат.
-
В поле Формат ячейки откройте вкладку Шрифт.
-
В списке Цвет выберите белый цвет и нажмите кнопку ОК.
Отображение нулей в виде пробелов или тире
Для решения этой задачи воспользуйтесь функцией ЕСЛИ.
Если ячейка содержит нулевые значения, для возврата пустой ячейки используйте формулу, например такую:
-
=ЕСЛИ(A2-A3=0;»»;A2-A3)
Вот как читать формулу. Если результат вычисления (A2-A3) равен «0», ничего не отображается, в том числе и «0» (это указывается двойными кавычками «»). В противном случае отображается результат вычисления A2-A3. Если вам нужно не оставлять ячейки пустыми, но отображать не «0», а что-то другое, между двойными кавычками вставьте дефис «-» или другой символ.
Скрытие нулевых значений в отчете сводной таблицы
-
Выберите отчет сводной таблицы.
-
На вкладке Анализ в группе Сводная таблица щелкните стрелку рядом с командой Параметры и выберите пункт Параметры.
-
Перейдите на вкладку Разметка и формат, а затем выполните следующие действия.
-
Изменение отображения ошибки В поле Формат установите флажок Для ошибок отображать. Введите в поле значение, которое нужно выводить вместо ошибок. Для отображения ошибок в виде пустых ячеек удалите из поля весь текст.
-
Изменение отображения пустой ячейки Установите флажок Для пустых ячеек отображать. Введите в поле значение, которое нужно выводить в пустых ячейках. Чтобы они оставались пустыми, удалите из поля весь текст. Чтобы отображались нулевые значения, снимите этот флажок.
-
К началу страницы
Потребность отображать нулевые значения (0) на листах возникает не всегда. Требуют ли стандарты форматирования или собственные предпочтения отображать или скрывать нулевые значения, имеется несколько способов реализации всех этих требований.
Отображение и скрытие всех нулевых значений на листе
-
Выберите Файл > Параметры > Дополнительно.
-
В группе Показать параметры для следующего листа выберите лист, после чего выполните одно из указанных ниже действий.
-
Чтобы отображать в ячейках нулевые значения (0), установите флажок Показывать нули в ячейках, которые содержат нулевые значения.
-
Чтобы отображать нулевые значения в виде пустых ячеек, снимите флажок Показывать нули в ячейках, которые содержат нулевые значения.
-
Скрытие нулевых значений в выделенных ячейках с помощью числового формата
Эти действия позволяют скрыть нулевые значения в выделенных ячейках. Если значение в одной из ячеек станет ненулевым, его формат будет аналогичен общему числовому формату.
-
Выделите ячейки, содержащие нулевые значения (0), которые требуется скрыть.
-
Вы можете нажать клавиши CTRL+1 или на вкладке Главная щелкнуть Формат > Формат ячеек.
-
В списке Категория выберите элемент Пользовательский.
-
В поле Тип введите 0;-0;;@
Примечания:
-
Скрытые значения отображаются только в строке формул или в ячейке, если вы редактируете ее содержимое. Эти значения не печатаются.
-
Чтобы снова отобразить скрытые значения, выделите ячейки, а затем нажмите клавиши CTRL+1 или на вкладке Главная в группе Ячейки наведите указатель мыши на элемент Формат и выберите Формат ячеек. Чтобы применить числовой формат по умолчанию, в списке Категория выберите Общий. Чтобы снова отобразить дату и время, выберите подходящий формат даты и времени на вкладке Число.
Скрытие нулевых значений, возвращенных формулой, с помощью условного форматирования
-
Выделите ячейку, содержащую нулевое (0) значение.
-
На вкладке Главная в группе Стили щелкните стрелку рядом с элементом Условное форматирование, наведите указатель на элемент Правила выделения ячеек и выберите вариант Равно.
-
В левом поле введите 0.
-
В правом поле выберите Пользовательский формат.
-
В диалоговом окне Формат ячеек откройте вкладку Шрифт.
-
В поле Цвет выберите белый цвет.
Использование формулы для отображения нулей в виде пробелов или тире
Для выполнения этой задачи используйте функцию ЕСЛИ.
Пример
Чтобы этот пример проще было понять, скопируйте его на пустой лист.
|
|
Дополнительные сведения об использовании этой функции см. в статье Функция ЕСЛИ.
Скрытие нулевых значений в отчете сводной таблицы
-
Щелкните отчет сводной таблицы.
-
На вкладке Параметры в группе Параметры сводной таблицы щелкните стрелку рядом с командой Параметры и выберите пункт Параметры.
-
Перейдите на вкладку Разметка и формат, а затем выполните следующие действия.
Изменение способа отображения ошибок. В поле Формат установите флажок Для ошибок отображать. Введите в поле значение, которое нужно выводить вместо ошибок. Для отображения ошибок в виде пустых ячеек удалите из поля весь текст.
Изменение способа отображения пустых ячеек. Установите флажок Для пустых ячеек отображать. Введите в поле значение, которое нужно выводить в пустых ячейках. Чтобы они оставались пустыми, удалите из поля весь текст. Чтобы отображались нулевые значения, снимите этот флажок.
К началу страницы
Потребность отображать нулевые значения (0) на листах возникает не всегда. Требуют ли стандарты форматирования или собственные предпочтения отображать или скрывать нулевые значения, имеется несколько способов реализации всех этих требований.
Отображение и скрытие всех нулевых значений на листе
-
Нажмите кнопку Microsoft Office
, Excel параметры, а затем выберите категорию Дополнительные параметры. -
В группе Показать параметры для следующего листа выберите лист, после чего выполните одно из указанных ниже действий.
-
Чтобы отображать в ячейках нулевые значения (0), установите флажок Показывать нули в ячейках, которые содержат нулевые значения.
-
Чтобы отображать нулевые значения в виде пустых ячеек, снимите флажок Показывать нули в ячейках, которые содержат нулевые значения.
-
, Excel параметры, а затем выберите категорию Дополнительные параметры.Скрытие нулевых значений в выделенных ячейках с помощью числового формата
Эти действия позволяют скрыть нулевые значения в выделенных ячейках. Если значение в одной из ячеек станет ненулевым, его формат будет аналогичен общему числовому формату.
-
Выделите ячейки, содержащие нулевые значения (0), которые требуется скрыть.
-
Вы можете нажать клавиши CTRL+1 или на вкладке Главная в группе Ячейки щелкнуть Формат > Формат ячеек.
-
В списке Категория выберите элемент Пользовательский.
-
В поле Тип введите 0;-0;;@
Примечания:
-
Скрытые значения отображаются только в
или в ячейке, если вы редактируете ячейку, и не печатаются. -
Чтобы снова отобразить скрытые значения, выделите ячейки, а затем на вкладке Главная в группе Ячейки наведите указатель мыши на элемент Формат и выберите Формат ячеек. Чтобы применить числовой формат по умолчанию, в списке Категория выберите Общий. Чтобы снова отобразить дату и время, выберите подходящий формат даты и времени на вкладке Число.
или в ячейке, если вы редактируете ячейку, и не печатаются.Скрытие нулевых значений, возвращенных формулой, с помощью условного форматирования
-
Выделите ячейку, содержащую нулевое (0) значение.
-
На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите «Правила выделения ячеек» > «Равно».
-
В левом поле введите 0.
-
В правом поле выберите Пользовательский формат.
-
В диалоговом окне Формат ячеек откройте вкладку Шрифт.
-
В поле Цвет выберите белый цвет.
Использование формулы для отображения нулей в виде пробелов или тире
Для выполнения этой задачи используйте функцию ЕСЛИ.
Пример
Чтобы этот пример проще было понять, скопируйте его на пустой лист.
Копирование примера
-
Выделите пример, приведенный в этой статье.
Важно: Не выделяйте заголовки строк или столбцов.
Выделение примера в справке
-
Нажмите клавиши CTRL+C.
-
В Excel создайте пустую книгу или лист.
-
Выделите на листе ячейку A1 и нажмите клавиши CTRL+V.
Важно: Чтобы пример правильно работал, его нужно вставить в ячейку A1.
-
Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих эти результаты, нажмите клавиши CTRL+` (знак ударения) или на вкладке Формулы в группе «Зависимости формул» нажмите кнопку Показать формулы.
Скопировав пример на пустой лист, вы можете настроить его так, как вам нужно.
|
|
Дополнительные сведения об использовании этой функции см. в статье Функция ЕСЛИ.
Скрытие нулевых значений в отчете сводной таблицы
-
Щелкните отчет сводной таблицы.
-
На вкладке Параметры в группе Параметры сводной таблицы щелкните стрелку рядом с командой Параметры и выберите пункт Параметры.
-
Перейдите на вкладку Разметка и формат, а затем выполните следующие действия.
Изменение способа отображения ошибок. В поле Формат установите флажок Для ошибок отображать. Введите в поле значение, которое нужно выводить вместо ошибок. Для отображения ошибок в виде пустых ячеек удалите из поля весь текст.
Изменение способа отображения пустых ячеек. Установите флажок Для пустых ячеек отображать. Введите в поле значение, которое нужно выводить в пустых ячейках. Чтобы они оставались пустыми, удалите из поля весь текст. Чтобы отображались нулевые значения, снимите этот флажок.
См. также
Полные сведения о формулах в Excel
Рекомендации, позволяющие избежать появления неработающих формул
Поиск ошибок в формулах
Сочетания клавиш и горячие клавиши в Excel
Функции Excel (по алфавиту)
Функции Excel (по категориям)
Нужна дополнительная помощь?
Вам когда-нибудь приходилось импортировать или вводить в 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 цифр. Цифровые коды также можно отформатировать с использованием дефисов или других знаков препинания. Например, чтобы сделать телефонные номера более удобными для чтения, можно добавить дефисы между международным кодом, кодом области, префиксом и остальными цифрами.
|
Цифровой код |
Пример |
Пользовательский числовой формат |
|---|---|---|
|
Социальные сети |
012345678 |
000-00-0000 |
|
Телефон |
0012345556789 |
00-0-000-000-000-0000 |
|
Почтовый |
00123 |
00000 |
Инструкции
-
Выделите ячейку или диапазон ячеек, которые вы хотите отформатировать.
-
Нажмите клавиши CTRL+1, чтобы открыть диалоговое окно Формат ячеек.
-
Откройте вкладку Число и в списке Числовые форматы выберите вариант (все форматы), а затем в поле Тип введите формат числа, например 000-00-0000 для кода социального страхования или 000000 для шестизначного почтового индекса.
Совет: Можно также выбрать формат Дополнительный, а затем тип Почтовый индекс, Индекс + 4, Номер телефона или Табельный номер.
Дополнительные сведения о пользовательских кодах см. в статье Создание и удаление пользовательских числовых форматов.
Примечание: Этот способ не позволяет восстановить начальные нули, которые были удалены до форматирования. Он действует только на числа, введенные после применения формата.
Применение функции ТЕКСТ для форматирования
Для преобразования данных в нужный формат можно использовать расположенный рядом с ними пустой столбец и функцию ТЕКСТ.
|
Цифровой код |
Пример (в ячейке A1) |
Функция ТЕКСТ и новый формат |
|---|---|---|
|
Социальные сети |
012345678 |
=ТЕКСТ(A1;»000-00-0000″) |
|
Телефон |
0012345556789 |
=ТЕКСТ(A1;»00-0-000-000-0000″) |
|
Почтовый |
00123 |
=ТЕКСТ(A1;»00000″) |
Округление номеров кредитных карт
Excel с максимальной точностью до 15 значащих цифр, то есть для любого числа, содержащего не менее 16 цифр, например номера кредитной карты, все числа, которые находятся после 15-й цифры, округляются до нуля. В случае числового кода, который составляет 16 цифр или больше, необходимо использовать текстовый формат. Для этого можно сделать одно из двух:
-
Форматирование столбца в виде текста
Выделите диапазон данных и нажмите клавиши CTRL+1, чтобы открыть диалоговое окно Формат > Ячейки. На вкладке Число выберите формат Текстовый.
Примечание: Этот способ не позволяет изменить числа, которые были введены ранее. Он действует только на числа, введенные после применения формата.
-
Использование апострофа
Перед числом можно ввести апостроф (‘), и Excel будет обрабатывать его как текст.
К началу страницы
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.