Вы когда-нибудь импортировали или вводили в Excel данные, содержащие начальные нули, например 00123, или большие числа, такие как 1234 5678 9087 6543? Это могут быть номера социального страхования, телефонные номера, номера кредитных карт, коды продуктов, номера счетов или почтовые индексы. Excel автоматически удаляет начальные нули и преобразует большие числа в экспоненциальное представление (например, 1,23E+15), чтобы их можно было использовать в формулах и математических операциях. В этой статье объясняется, как сохранить данные в исходном формате, который Excel обрабатывает как текст.
Настройка автоматического преобразования данных
Важно
Эта функция доступна в Excel для Microsoft 365, Excel для Microsoft 365 для Mac, Excel 2024, Excel 2024 для Mac.
С помощью функции автоматического преобразования данных Excel можно изменить поведение Excel по умолчанию для следующих автоматических преобразований данных:
- Удаление нулей в начале числового текста и преобразование их в число.
- Усечение числовых данных с точностью до 15 цифр и преобразование их в число, отображаемое в экспоненциальном представлении.
- Преобразование числовых данных вокруг буквы "E" в экспоненциальное представление.
- Преобразование непрерывной строки букв и цифр в дату.
Дополнительные сведения см. в разделе Настройка автоматических преобразований данных.
Преобразование чисел в текст при импорте текстовых данных
Для форматирования отдельных столбцов в виде текста при импорте данных в Excel используйте функцию Получить и преобразовать (Power Query). В этом случае импортируется текстовый файл, однако данные проходят те же этапы преобразования, что и при импорте из других источников, таких как XML, Интернет, JSON и т. д.
Перейдите на вкладку " Данные ", выберите пункт "Из текстового/CSV-файла " рядом с кнопкой "Получить данные ". Если кнопка "Получить данные" не отображается, перейдите в раздел "Создать запрос>из файла>из текста", перейдите к текстовому файлу и нажмите кнопку "Импорт".
Excel загрузит данные в область предварительного просмотра. В области предварительного просмотра нажмите кнопку Изменить, чтобы загрузить Редактор запросов.
Если какие-либо столбцы нужно преобразовать в текст, выделите столбец для преобразования, щелкнув его заголовок, а затем перейдите в пункт"Тип> данных для преобразования домашняя> страница" и выберите">Текст".
Совет
Чтобы выбрать несколько столбцов, щелкните их левой кнопкой мыши, удерживая нажатой клавишу CTRL.
Затем выберите "Заменить текущий " в диалоговом окне " Изменить тип столбца ", и Excel преобразует выделенные столбцы в текст.
По завершении нажмите кнопку " Закрыть" & "Загрузить", и Excel вернет данные запроса на лист.
Если данные в будущем изменятся, можно перейти в раздел"Обновлениеданных>", и Excel автоматически обновит данные и применит преобразования.
Применение пользовательского формата для сохранения начальных нулей
Если эту задачу необходимо решить в пределах книги, поскольку она не используется другими программами в качестве источника данных, для сохранения начальных нулей можно использовать пользовательский или специальный формат. Этот способ подходит для цифровых кодов, содержащих менее 16 цифр. Цифровые коды также можно отформатировать с использованием дефисов или других знаков препинания. Например, чтобы сделать телефонные номера более удобными для чтения, можно добавить дефисы между международным кодом, кодом области, префиксом и остальными цифрами.
| Цифровой код | Пример | Пользовательский числовой формат |
|---|---|---|
| Социальные сети безопасность |
012345678 | 000-00-0000 012-34-5678 |
| Телефон | 0012345556789 | 00-0-000-000-0000 00-1-234-555-6789 |
| Почтовый код |
00123 | 00000 00123 |
Действия
Выделите ячейку или диапазон ячеек, которые вы хотите отформатировать.
Нажмите клавиши CTRL+1, чтобы открыть диалоговое окно Формат ячеек.
Откройте вкладку " Число ", затем в списке "Категория " выберите "Настраиваемый ", а затем в поле " Тип " введите числовой формат, например 000-00-0000 для номера социального страхования или 00000 для пятизначного почтового индекса.
Совет
Вы также можете выбрать "Специальный", а затем выбрать "Почтовый индекс", " Почтовый индекс + 4", "Номер телефона" или "Номер социального страхования".
Дополнительные сведения о пользовательских кодах см. в статье Создание и удаление пользовательских числовых форматов.
Примечание
Этот способ не позволяет восстановить начальные нули, которые были удалены до форматирования. Он действует только на числа, введенные после применения формата.
Применение функции ТЕКСТ для форматирования
Для преобразования данных в нужный формат можно использовать расположенный рядом с ними пустой столбец и функцию ТЕКСТ.
| Цифровой код | Пример (в ячейке A1) | Функция ТЕКСТ и новый формат |
|---|---|---|
| Социальные сети безопасность |
012345678 | =ТЕКСТ(A1;"000-00-0000") 012-34-5678 |
| Телефон | 0012345556789 | =ТЕКСТ(A1;"00-0-000-000-000") 00-1-234-555-6789 |
| Почтовый код |
00123 | =ТЕКСТ(A1;"00000") 00123 |
Округление номеров кредитных карт
Максимальная точность Excel составляет 15 значащих цифр. Это означает, что для любого числа, содержащего 16 или более цифр, например номера кредитной карты, любые числа после 15-й цифры округляются до нуля. Для кодов чисел, содержащих 16 цифр или более, необходимо использовать текстовый формат. Для этого можно выполнить одно из двух действий:
Форматирование столбца в виде текста
Выберите диапазон данных и нажмите клавиши CTRL+1 , чтобы открыть диалоговое окно "Формат ячеек ". На вкладке "Число " выберите "Текст".Примечание
Этот способ не позволяет изменить числа, которые были введены ранее. Он действует только на числа, введенные после применения формата.
Использование апострофа
Перед числом можно ввести апостроф ('), и Excel будет обрабатывать его как текст.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.