Как в эксель сохранить число как текст
Нередко мы копируем данные в excel из веба либо редакторов текста (word). В итоге этого сталкиваемся с неувязкой, что некие числа вставились как текст, либо ячейки с датами преобразовались в числовые значения. Сохраненные как текст числа не участвуют в расчетах и приводят к суровым математическим ошибкам. Не сходится сумма ячеек, посчитанная в excel со значением на калькуляторе. Ужаснее всего то, что отыскать какое же число сохранено как текст достаточно проблематично (в особенности если в файле не одна тыща ячеек с данными). Надстройка VBA-Excel поможет стремительно отыскать ошибку.
Решение задачи
Решение задачки достаточно обычное — залить все ячейки заливкой в согласовании с типом данных в их. Т.е. числовые значения одним цветом, текстовые вторым и так дальше (даты, формулы, логические выражения, ошибки). Для этого пригодится:
- Выделить спектр, в каком нужно выстроить цветовую схему данных
- Перейти на вкладку VBA-Excel
- В меню Подсветка избрать команду Цветовая карта данных
Получаем последующую картину.
Наглядно видно, что цвет 4 ячеек с числами различается от других. Цвет заливки гласит нам, что формат данных ячеек текстовый. Это приводило к тому, что они игнорировались при суммировании.
Опосля того, как ошибки будут исправлены, отключите цветовую карту данных нажав на подобающую команду в меню Подсветка.
При импорте файлов либо копировании данных с числовыми значениями нередко возникает неувязка: число преобразуется в текст. В итоге формулы не работают, вычисления стают неосуществимыми. Как это стремительно поправить? Поначалу поглядим, как поправить ошибку без макросов.
Как конвертировать текст в число в Excel
Excel помогает юзеру сходу найти, значения в ячейках отформатированы как числа либо как текст. Числовые форматы выравниваются по правому краю, текстовые – по левому.
Когда при импорте файлов либо нарушении в Excel числовой формат становится текстовым, в левом верхнем углу ячеек возникает зеленоватый треугольничек. Это символ ошибки. Ошибка также возникает, если перед числом поставить апостроф.
Методов преобразования текста в число существует несколько. Разглядим самые обыкновенные и комфортные.
- Применять меню клавиши «Ошибка». При выделении хоть какой ячейки с ошибкой слева возникает соответственный значок. Это и есть клавиша «Ошибка». Если навести на нее курсор, покажется символ раскрывающегося меню (темный треугольник). Выделяем столбец с числами в текстовом формате. Раскрываем меню клавиши «Ошибка». Жмем «Конвертировать в число».
- Применить любые математические деяния. Подходят простые операции, которые не изменяют итог (умножение / деление на единицу, прибавление / отнимание нуля, возведение в первую степень и т.д.).
- Добавить специальную вставку. Тут также применяется обычное арифметическое действие. Но вспомогательный столбец создавать не надо. В отдельной ячейке написать цифру 1. Скопировать ячейку в буфер обмена (при помощи клавиши «Копировать» либо сочетания кнопок Ctrl + C). Выделить столбец с редактируемыми числами. В контекстном меню клавиши «Вставить» надавить «Особая вставка». В открывшемся окне установить галочку напротив «Помножить». Опосля нажатия ОК текстовый формат преобразуется в числовой.
- Удаление непечатаемых знаков. Время от времени числовой формат не распознается программкой из-за невидимых знаков. Удалим их при помощи формулы, которую введем во вспомогательный столбец. Функция ПЕЧСИМВ удаляет непечатаемые знаки. СЖПРОБЕЛЫ – излишние пробелы. Функция ЗНАЧЕН конвертирует текстовый формат в числовой.
- Применение инструмента «Текст по столбцам». Выделяем столбец с текстовыми аргументами, которые необходимо конвертировать в числа. На вкладке «Данные» находим клавишу «Текст по столбцам». Раскроется окно «Мастера». Жмем «Дальше». На 3-ем шаге обращаем внимание на формат данных столбца.
Крайний метод подступает в этом случае, если значения находятся в одном столбце.
Макрос «Текст – число»
Конвертировать числа, сохраненные как текст, в числа можно при помощи макроса.
Есть набор значений, сохраненных в текстовом формате:
Чтоб вставить макрос, на вкладке «Разраб» находим редактор Visual Basic. Раскрывается окно редактора. Для прибавления кода жмем F7. Вставляем последующий код:
Чтоб он «заработал», необходимо сохранить. Но книжка Excel обязана быть сохранена в формате с поддержкой макросов.
Сейчас возвращаемся на страничку с цифрами. Выделяем столбец с данными. Жмем клавишу «Макросы». В открывшемся окне – перечень доступных для данной книжки макросов. Избираем подходящий. Нажимаем «Выполнить».
Числа переместились на право.
Как следует, значения в ячейках «стали» числами.
Если в столбце встречаются аргументы с определенным числом десятичных символов (к примеру, 3,45), можно применять иной макрос.
Таковым образом, возникающую при импорте либо копировании числовых данных ошибку просто убрать. Конвертировать текст в число можно различными методами (при помощи макросов и без их). Создать это просто и стремительно. А потом с числовыми аргументами выполняются все нужные операции.
Примечание: Мы стараемся как можно оперативнее обеспечивать вас животрепещущими справочными материалами на вашем языке. Эта страничка переведена автоматом, потому ее текст может содержать некорректности и грамматические ошибки. Для нас принципиально, чтоб эта статья была для вас полезна. Просим вас уделить пару секунд и сказать, посодействовала ли она для вас, при помощи клавиш понизу странички. Для удобства также приводим ссылку на оригинал (на британском языке).
Если вы желаете, чтоб в Excel числа определенных типов воспринимались как текст, используйте заместо числового формата текстовый. К примеру, если вы используете номера кредитных карт либо остальные коды, содержащие 16 цифр либо наиболее, нужно применять текстовый формат. Это обосновано тем, что Excel имеет максимум 15 цифр и Округляет все числа, которые следуют за 15 цифрами до нуля, что быть может не так, как вы желаете.
Если число имеет текстовый формат, это просто найти, так как число будет выровнено в ячейке по левому краю, а не по правому.
Выделите ячейку либо спектр ячеек, содержащих числа, которые необходимо отформатировать как текст. Выделение ячеек и диапазонов.
Совет: Можно также выделить пустые ячейки, отформатировать их как текст, а потом ввести числа. Такие числа будут иметь текстовый формат.
На вкладке Основная в группе число щелкните стрелку рядом с полем числовой формат и изберите пункт текст.
Примечание: Если вы не видите параметр текст , прокрутите перечень до конца перечня при помощи полосы прокрутки.
Чтоб применять десятичные знаки в числах, хранящихся как текст, может быть, придется вводить эти числа с десятичными разделителями.
При вводе числа, начинающегося с нуля (к примеру, кода продукта), этот ноль по дефлоту удаляется. Если требуется сохранить ноль, можно сделать пользовательский числовой формат, который не дозволит приложению Excel удалять исходные нули в числах. К примеру, при вводе десятизначного кода продукта Excel по дефлоту изменяет число 0784367998 на 784367998. В данном случае можно сделать пользовательский числовой формат с кодом 0000000000, чтоб в Excel показывались все 10 символов кода продукта, включая исходный ноль. Доп сведения о данной дилемме см. в статьях Создание и удаление пользовательских числовых форматов и Сохранение исходных нулей в числовых кодах.
Время от времени числа могут быть отформатированы и сохранены в ячейках как текст, что потом может привести к дилеммам при вычислениях либо кавардаку при сортировке. Это время от времени случается при импорте либо копировании чисел из базы данных либо другого источника данных. В таковой ситуации требуется оборотное преобразование чисел, сохраненных в виде текста, в числовой формат. Доп сведения см. в статье Преобразование чисел из текстового формата в числовой.
Вы также сможете применять функцию ТЕКСТ для преобразования числа в текст с определенным числовым форматом. Примеры использования этого способа см. в статье Сохранение исходных нулей в числовых кодах. Сведения о использовании функции текст можно отыскать в разделе функция текст.
