Как отформатировать число как текст
Форматирование чисел в виде текста
Если вы хотите, чтобы в Excel числа определенных типов воспринимались как текст, используйте вместо числового формата текстовый. Например, при использовании номеров кредитных карт или других числового кода, содержащих не менее 16 цифр, необходимо использовать текстовый формат. Это происходит потому Excel что точность не может быть больше 15 цифр, и она округлит все числа после 15-й цифры до нуля, что, вероятно, вас не захотнет.
Если число имеет текстовый формат, это легко определить, поскольку число будет выровнено в ячейке по левому краю, а не по правому.
Выберем ячейку или диапазон, которые содержат числа, которые вы хотите отформать как текст. Выбор ячеек или диапазона.
Совет: Можно также выделить пустые ячейки, отформатировать их как текст, а затем ввести числа. Такие числа будут иметь текстовый формат.
На вкладке Главная в группе Число щелкните стрелку рядом с полем Числовом формате и выберите текст.
Примечание: Если вы не видите параметр Текст, прокрутите список с помощью ручений.
Чтобы использовать десятичные знаки в числах, хранящихся как текст, возможно, придется вводить эти числа с десятичными разделителями.
При вводе числа, начинающегося с нуля (например, кода продукта), этот ноль по умолчанию удаляется. Если требуется сохранить ноль, можно создать пользовательский числовой формат, который не позволит приложению Excel удалять начальные нули в числах. Например, при вводе десятизначного кода продукта Excel по умолчанию изменяет число 0784367998 на 784367998. В данном случае можно создать пользовательский числовой формат с кодом 0000000000, чтобы в Excel отображались все десять знаков кода продукта, включая начальный ноль. Дополнительные сведения об этой проблеме см. в статьях Создание и удаление пользовательских числовых форматов и Сохранение начальных нулей в числовых кодах.
Иногда числа могут быть отформатированы и сохранены в ячейках как текст, что впоследствии может привести к проблемам при вычислениях или беспорядку при сортировке. Это иногда случается при импорте или копировании чисел из базы данных или другого источника данных. В такой ситуации требуется обратное преобразование чисел, сохраненных в виде текста, в числовой формат. Дополнительные сведения см. в статье Преобразование чисел из текстового формата в числовой.
Вы также можете использовать функцию ТЕКСТ для преобразования числа в текст с определенным числовым форматом. Примеры использования этого метода см. в статье Сохранение начальных нулей в числовых кодах. Сведения об использовании функции ТЕКСТ см. в документе Функция ТЕКСТ.
Как преобразовать числа в текст в Excel — 4 простых способа
Excel — отличный инструмент, который можно использовать для ввода данных, в качестве базы данных, а также для анализа данных и создания информационных панелей и отчетов.
Хотя большинство встроенных функций и настроек по умолчанию предназначены для того, чтобы быть полезными и экономить время пользователей, иногда вам может потребоваться немного изменить ситуацию.
Преобразование чисел в текст — один из таких сценариев.
Зачем преобразовывать числа в текст в Excel?
При работе с числами в Excel лучше всего использовать их только как числа. Но в некоторых случаях наличие номера может стать проблемой.
Давайте рассмотрим несколько сценариев, в которых наличие чисел создает проблемы для пользователей.
Сохранение ведущих нулей
Например, если вы введете 001 в ячейку в Excel, вы заметите, что Excel автоматически удаляет ведущие нули (поскольку считает, что они не нужны).
Хотя в большинстве случаев это не проблема (поскольку вы не стали бы начинать нули), в случае, если они вам действительно нужны, одно из решений — преобразовать эти числа в текст.
Таким образом, вы получите именно то, что вводите.
Один из распространенных сценариев, когда вам это может понадобиться, — это когда вы работаете с большими числами, такими как SSN или идентификаторы сотрудников с ведущими нулями.
Ввод больших числовых значений
Знаете ли вы, что в Excel можно ввести только числовое значение, состоящее из 15 цифр? Если вы введете 16-значное число, оно изменит 16-ю цифру на 0.
Поэтому, если вы работаете с SSN, номерами счетов или любым другим типом больших чисел, существует вероятность, что ваши входные данные автоматически изменяются Excel.
И что еще хуже, вы не получаете никаких подсказок или ошибок. Он просто меняет цифры на 0 после 15-й цифры.
Опять же, об этом позаботятся, если преобразовать число в текст.
Преобразование чисел в даты
Это порадовало многих (в том числе и меня).
Попробуйте ввести 01-01 в Excel, и он автоматически изменит его на дату (01 января текущего года).
Поэтому, если вы введете что-либо, что является допустимым форматом даты в Excel, оно будет преобразовано в дату.
Многие люди обращаются ко мне по этому поводу, поскольку они хотят вводить оценки в Excel в этом формате, но в конечном итоге разочаровываются, когда вместо этого видят даты. Опять же, изменение формата ячейки с числа на текст поможет сохранить оценки как есть.
Теперь давайте продолжим и рассмотрим некоторые методы, которые вы можете использовать для преобразования чисел в текст в Excel.
Преобразование чисел в текст в Excel
В этом разделе я расскажу о четырех различных способах преобразования чисел в текст в Excel.
Не все эти методы одинаковы, и некоторые из них могут быть более подходящими, чем другие, в зависимости от вашей ситуации.
Добавление апострофа
Если вы вручную вводите данные в Excel и не хотите, чтобы ваши числа автоматически меняли формат, вот простой трюк:
Добавьте апостроф (‘) перед числом
Поэтому, если вы хотите ввести 001, введите «001» (где перед числом стоит апостроф).
И не волнуйтесь, в ячейке не видно апострофа. Вы увидите только номер.
Когда вы добавляете апостроф перед числом, он превращается в текст, а также добавляется небольшой зеленый треугольник в верхней левой части ячейки (как показано на рисунке). Это способ Exel сообщить вам, что в ячейке есть число, преобразованное в текст.
Когда вы добавляете апостроф перед числом, он сообщает Excel, что все последующее следует рассматривать как текст.
Быстрый способ визуально проверить, отформатированы ли ячейки как текст или нет, — это увидеть, выровнены ли числа слева направо. Когда числа отформатированы как текст, они по умолчанию выравниваются по правому краю (так как они идут влево).
Добавление апострофа изменяет выравнивание ячеек влево
Даже если вы добавите апостроф перед числом в ячейке в Excel, вы все равно можете использовать их как числа в вычислениях.
Точно так же, если вы хотите ввести 01-01, добавление апострофа гарантирует, что он не изменится на дату.
Хотя этот метод работает во всех случаях, он полезен только в том случае, если вы вручную вводите несколько чисел в Excel. Если вы вводите много данных в определенный диапазон строк / столбцов, используйте следующий метод.
Преобразование формата ячейки в текст
Другой способ убедиться, что любая числовая запись в Excel считается текстовым значением, — это изменить формат ячейки.
Таким образом, вам не нужно беспокоиться о вводе апострофа каждый раз, когда вы вводите данные вручную.
Вы можете продолжить ввод данных, как обычно, и Excel позаботится о том, чтобы ваши числа не изменились.
Вот как это сделать:
Вышеупомянутые шаги изменили бы форматирование ячеек по умолчанию с общего на текстовое.
Теперь, если вы введете в эти ячейки любое число или любую текстовую строку, она автоматически будет считаться текстовой строкой.
Это означает, что Excel не будет автоматически изменять вводимый вами текст (например, усекать начальные нули или преобразовывать введенные числа в даты).
В этом примере, хотя я сначала изменил форматирование ячеек перед вводом данных, вы также можете сделать это с ячейками, в которых уже есть данные.
Но помните, что если вы уже ввели числа, которые были изменены в Excel (например, удалили ведущие нули или заменили текст датами), это не вернется. Вам придется снова ввести эти данные.
Также имейте в виду, что форматирование ячеек может измениться, если вы скопируете и вставите в эти ячейки некоторые данные. При обычном копипасте он также копировал форматирование из скопированных ячеек. Так что лучше копировать и вставлять только значения.
Использование функции TEXT
В Excel есть встроенная функция TEXT, которая предназначена для преобразования числового значения в текстовое значение (где вы должны указать формат текста, в котором вы хотите получить окончательный результат).
Этот метод полезен, когда у вас уже есть набор чисел и вы хотите показать их в более удобочитаемом формате или если вы хотите добавить текст в качестве суффикса или префикса к этим числам.
Предположим, у вас есть набор чисел, как показано ниже, и вы хотите показать все эти числа в виде пятизначных значений (что означает добавление начальных нулей к числам, длина которых меньше 5 цифр).
Хотя Excel удаляет все начальные нули в числах, вы можете использовать приведенную ниже формулу, чтобы получить это:
В приведенной выше формуле я использовал «00000» в качестве второго аргумента, который сообщает формуле желаемый формат вывода. В этом примере 00000 будет означать, что мне нужно, чтобы все числа были как минимум пятизначными.
Вы можете сделать гораздо больше с помощью функции TEXT — например, добавить символ валюты, добавить префикс или суффикс к числам или изменить формат, чтобы он содержал запятую или десятичные дроби.
Использование этого метода может быть полезно, если у вас уже есть данные в Excel и вы хотите отформатировать их определенным образом.
Это также может быть полезно для экономии времени при ручном вводе данных, когда вы можете быстро ввести данные, а затем использовать функцию TEXT, чтобы получить их в желаемом формате.
Использование текста в столбцы
Еще один быстрый способ преобразования чисел в текст в Excel — использование мастера Text to Columns ( Текст в столбцы).
Хотя цель Text to Columns — разбить данные на несколько столбцов, у него есть настройка, которая также позволяет нам быстро выбирать диапазон ячеек и преобразовывать все числа в текст с помощью нескольких щелчков мышью.
Предположим, у вас есть набор данных, показанный ниже, и вы хотите преобразовать все числа в столбце A в текст.
Ниже приведены шаги для этого:
Вышеупомянутые шаги мгновенно преобразовали бы все эти числа в столбце A в текст. Вы заметили, что числа теперь будут выровнены по правому краю (что означает, что содержимое ячейки является текстом).
В верхней левой части ячейки также будет небольшой зеленый треугольник, с помощью которого Excel сообщает вам, что есть числа, которые хранятся в виде текста.
Если вам нужно это только для нескольких ячеек, в которые вы будете вводить данные вручную, я предлагаю вам использовать метод апострофа. Если вам нужно ввести данные для более чем нескольких ячеек, вы можете попробовать изменить формат ячеек с общего на текстовый.
А если у вас уже есть числа в Excel и вы хотите преобразовать их в текст, вы можете использовать метод формулы TEXT или метод текста в столбцы, который я рассмотрел в этом руководстве.
Вводим в ячейку число как текст
Часто в Excel числа необходимо записать как текст. И на оборот, введенные нами текстовые значения программа меняет на числа или даты.
На первом уроке рассмотрим, в каких случаях может возникнуть конфликт между пользователем и программой при введении данных в ячейки. Так же найдем лучшее решения для правильного введения в ячейки числа как текст.
Как преобразовать число в текст?
Вводить числа как текст в Excel приходится достаточно часто. Например:
Поэтому важно научиться управлять форматами ячеек.
Попробуйте заполнить табличку, так как показано на рисунке:
Как видно на примере проблема возникает при введении текста «10/12». После нажатия клавиши «Enter» получаем дату:
Необходимо заставить Excel распознавать числа как текст.
Главным отличием выше описанных двух примеров является закрепление за ячейкой способа отображения ее значений с помощью присвоения формата. Если в первом решении в ячейку будут вводиться новые типы данных ( без апострофа ), то они будут отображаться по умолчанию.
Математические операции с текстом в Excel
Если функция ссылается на ячейку, в которой число записано текстом, то при вычислении значение этой ячейки равно «0». Но здесь нужно быть очень внимательным, так как бывают исключения. Это может приводить к ошибочным расчетам. Приводим конкретный пример.
Теперь обратите внимание на результаты вычислений функций и формул в колонке D.
В ячейке D2 функция =СУММ(A2:C2) отображает правильный результат 1+100+10=111. А в ячейке D3 функция суммирования меняет значение ячейки B3 на число 0, так как там данные отображаются в текстовом формате. В результате: 1+0+10=11.
Внимание! При вычислении аналогичных значений ячеек, но уже с помощью обычной формулы =A6+B6+C6 все значения ячеек воспринимаются как числовые значения, несмотря на то, что ячейка B6 имеет текстовый формат. Формулы могут суммировать, умножать, разделять и вычитать текст, если он состоит только из символов чисел (0-9), а функции не могут.
Из этого примера возникает простой вывод. При вычислении и расчетах в Excel необходимо использовать одинаковые типы данных, не зависимо от формул и функций. Это лучше чем конвертировать текст в число и позволит избежать много ошибок. Не выполняйте никаких математических операций с текстом.
Как преобразовать текст в число в Excel — 10 способов.
В этом руководстве показано множество различных способов преобразования текста в число в Excel: опция проверки ошибок в числах, формулы, математические операции, специальная вставка и многое другое.
Перестает правильно работать сортировка данных, поскольку числовые и текстовые значения упорядочиваются по-разному. Функции поиска, подобные ВПР, также не могут найти нужные значения (подробнее об этом читайте – Почему не работает ВПР?). Подсчет по условиям СУММЕСЛИ и СЧЁТЕСЛИ даст неверные результаты. Если они находятся среди «нормальных» чисел, то функция СУММ их проигнорирует, а вы этого даже не заметите. В результате – неверные расчеты.
Из этого материала вы узнаете, как преобразовать строки в «настоящие» числа.
Как определить числа, записанные как текст?
В некоторых случаях индикатор ошибки не отображается для чисел, записанных в виде текста. Но есть и другие визуальные индикаторы текстовых чисел:
Число
Строка (текстовое значение)
На изображении ниже вы можете видеть текстовые представления чисел справа и реальные числа слева:
Есть несколько разных способов изменить текст на число Excel. Ниже мы рассмотрим их, начиная с самых быстрых и простых. Если простые методы не работают для вас, пожалуйста, не расстраивайтесь. Нет проблем, которые невозможно преодолеть. Просто нужно попробовать другие способы.
Используем индикатор ошибок.
Если в ваших клетках отображается индикатор ошибки (зеленый треугольник в верхнем левом углу), преобразование выполняется одним щелчком мыши:
Таким образом можно одним махом преобразовать в числа весь столбец. Просто выделите всю проблемную область, а затем жмите восклицательный знак.
Смена формата ячейки.
Все ячейки в Экселе имеют определенный формат, который указывает программе, как их обрабатывать. Например, даже если в клетке таблицы будут записаны цифры, но формат выставлен текстовый, то они будут рассматриваться как простой текст. Никакие подсчеты с ними вы провести не сможете. Для того, чтобы Excel воспринимал цифры как нужно, они должны быть записаны с общим или числовым форматом.
Итак, первый быстрый способ видоизменения заключается в следующем:
Или же можно воспользоваться контекстным меню, вызвав его правым кликом мышки.
Последовательность действий в этом случае показана на рисунке. В любом случае, нужно применить числовой либо общий формат.
Этот способ не слишком удобен и достался нам «в наследство» от предыдущих версий Excel, когда еще не было индикатора ошибки в виде зелёного уголка.
Примечание. Этот метод не работает в некоторых случаях. Например, если вы примените текстовый формат, запишете несколько цифр, а затем измените формат на «Числовой». Тут ячейка все равно останется отформатированной как текст.
То же самое произойдёт, если перед цифрами будет стоять апостроф. Это однозначно указывает Excel, что записан именно текст и ничто другое.
Специальная вставка.
По сравнению с предыдущими методами этот метод требует еще нескольких дополнительных шагов, но работает почти на 100%.
Чтобы исправить числа, отформатированные как текст с помощью специальной вставки, выполните следующие действия:
Если все сделано правильно, то ваши значения изменят выравнивание слева на правую сторону. Excel теперь воспринимает их как числа.
Инструмент «текст по столбцам».
Это еще один способ использовать встроенные возможности Excel. При использовании для других целей, например для разделения ячеек, мастер «Текст по столбцам» представляет собой многоэтапный процесс. А вот чтобы просто выполнить нашу метаморфозу, нажимаете кнопку Готово на самом первом шаге 🙂
Это все, что нужно сделать!
Повторный ввод.
Если проблемных ячеек, о которых мы ведём здесь разговор, у вас не очень много, то, возможно, неплохим вариантом будет просто ввести их заново.
Для этого сначала установите их формат на «Обычный». Затем в каждую из них введите цифры заново.
Но это, конечно, если таких «псевдо-чисел» немного. Иначе овчинка не стоит выделки. Есть много других менее трудоемких способов.
Преобразовать текст в число с помощью формулы
До сих пор мы обсуждали встроенные возможности, которые можно применить для перевода текста в число в Excel. Во многих ситуациях это может быть сделано быстрее с помощью формулы.
Функция ЗНАЧЕН может даже распознавать набор цифр, включающих некоторые «лишние» символы.
Например, распознает цифры, записанные с разделителем тысяч в виде пробела:
Конвертируем число, введенное с символом валюты и разделителем тысяч:
Обе эти формулы возвратят число 1000.
Точно так же она расправляется с пробелами перед цифрами.
Чтобы преобразовать столбец символьных значений в числа, введите выражение в первую позицию и перетащите маркер заполнения, чтобы скопировать его вниз по столбцу.
Функция ЗНАЧЕН также пригодится, когда вы извлекаете что-либо из символьной строки с помощью одной из текстовых функций, таких как ЛЕВСИМВ, ПРАВСИМВ и ПСТР.
Например, чтобы получить последние 3 символа из A2 и вернуть результат в виде цифр, используйте следующее:
На приведенном ниже рисунке продемонстрирована формула трансформации:
Если вы не обернете функцию ПРАВСИМВ в ЗНАЧЕН, результат будет возвращен в виде набора символов, что делает невозможным любые вычисления с извлеченными значениями.
Этот метод подходит, когда вы точно знаете, сколько символов и откуда вы желаете получить, а затем превратить их в число.
Математические операции.
Что это может быть? Например, сложение с нулём, умножение или деление на 1.
Важно, чтобы эти действия не изменили величины чисел. Выше вы видите пример таких операций: двойное умножение на минус 1, умножение на 1, сложение с 0. Наиболее элегантно и просто для ввода выглядит «двойное отрицание»: ставим два минуса перед ссылкой, то есть дважды умножаем на минус 1. Результат расчета не изменится, а записать такую формулу очень просто.
Но если исходные значения отформатированы как текст, Excel также может автоматически применить соответствующий формат и к полученным результатам. Вы сможете заметить это по выровненному влево их содержимому. Чтобы это исправить, обязательно установите общий формат для ячеек, которые используются в формуле.
Примечание. Если вы хотите, чтобы результаты были значениями, а не формулами, используйте после применения этого метода функцию специальной вставки, чтобы заменить их результатами.
Удаление непечатаемых символов.
Когда вы копируете в таблицу Excel данные из других приложений при помощи буфера обмена (то есть Копировать – Вставить), вместе с цифрами часто копируется и различный «мусор». Так в таблице могут появиться внешне не видимые непечатаемые символы. В результате ваши цифры будут восприниматься программой как символьная строка.
Эту напасть можно удалить программным путем при помощи формулы. Аналогично предыдущему примеру, в С2 можно записать примерно такое выражение:
Поясню, как это работает. Функция ПЕЧСИМВ удаляет непечатаемые знаки. СЖПРОБЕЛЫ – лишние пробелы. Функция ЗНАЧЕН, как мы уже говорили ранее, преобразует текст в число.
Макрос VBA.
Если вам часто приходится преобразовывать большие области данных из текстового формата в числовой, то есть резон для этих повторяющихся операций создать специальный макрос, который будет использоваться при необходимости. Но для того, чтобы это выполнить, прежде всего, нужно в Экселе включить макросы и панель разработчика, если это до сих пор не сделано. Нажмите правой кнопкой мыши на ленте и настройте показ этого раздела.
После этого закрываем редактор, выполнив нажатие стандартной кнопки закрытия в верхнем правом углу окна.
Что делает этот макрос?
Вы можете выделить несколько областей данных для конвертации (можно использовать мышку при нажатой клавише CTRL). При этом, если в ваших числах в качестве разделителя десятичных разрядов используется запятая, то она будет автоматически заменена на точку. Ведь в Windows чаще всего именно точка отделяет целую и дробную части числа. А при экспорте данных из других программ запятая в этой роли встречается почему-то очень часто.
Чтобы использовать этот код, выделяем область на рабочем листе, которую нужно преобразовать. Жмем на значок «Макросы», который расположен на вкладке «Разработчик» в группе «Код». Или нам поможет комбинация клавиш ALT+F8.
Открывается окно имеющихся макросов. Находим «Текст_в_число», указываем на его и жмем на кнопку «Выполнить».
Извлечь число из текстовой строки с помощью Ultimate Suite
Как вы уже убедились, не существует универсальной формулы Excel для извлечения числа из текстовой строки. Если у вас возникли трудности с пониманием формул или их настройкой для ваших наборов данных, вам может понравиться этот простой способ получить число из строки в Excel.
Надстройка Ultimate Suite предоставляет множество инструментов для работы с текстовыми значениями: удалить лишние пробелы и ненужные символы, изменить регистр текста, подсчитать символы и слова, добавить один и тот же текст в начало или конец всех ячеек в диапазоне, преобразовать текст в числа, разделить его по отдельным ячейкам, заменить ошибочные символы с правильными.
Вот как вы можете быстро получить число из любой буквенно-цифровой строки:
Я советую активировать эту возможность, если вы хотите, чтобы извлеченные числа обновлялись автоматически, как только в исходные строки вносятся какие-либо изменения.
Если вы хотите, чтобы результаты не зависели от исходных строк (например, если вы планируете удалить исходные данные позже), не выводите результат в виде формулы.
Сложновато самому написать такую формулу, не правда ли?
Если отсутствует флажок «Вставить как формулу», вы увидите число в строке формул.
Любопытно попробовать? Просто скачайте пробную версию Ultimate Suite и убедитесь сами 🙂
Если вы хотите иметь этот, а также более 60 других полезных инструментов в своем Excel, воспользуйтесь этой специальной возможностью покупки, которую предоставлена исключительно читателям нашего блога.
Вот как вы можете преобразовать текст в число Excel с помощью формул и встроенных функций. Более сложные случаи, когда в ячейке находятся одновременно и буквы, и цифры, мы рассмотрим в отдельной статье. Я благодарю вас за чтение и надеюсь не раз еще увидеть вас в нашем блоге!