Преобразовать текст в число с помощью формулы
До сих пор мы обсуждали встроенные возможности, которые можно применить для перевода текста в число в Excel. Во многих ситуациях это может быть сделано быстрее с помощью формулы.
В Microsoft Excel есть специальная функция – ЗНАЧЕН (VALUE в английском варианте). Она обрабатывает как текст в кавычках, так и ссылку на элемент таблицы, содержащий символы для трансформирования.
Функция ЗНАЧЕН может даже распознавать набор цифр, включающих некоторые «лишние» символы.
Например, распознает цифры, записанные с разделителем тысяч в виде пробела:
Конвертируем число, введенное с символом валюты и разделителем тысяч:
Обе эти формулы возвратят число 1000.
Точно так же она расправляется с пробелами перед цифрами.
Чтобы преобразовать столбец символьных значений в числа, введите выражение в первую позицию и перетащите маркер заполнения, чтобы скопировать его вниз по столбцу.
Функция ЗНАЧЕН также пригодится, когда вы извлекаете что-либо из символьной строки с помощью одной из текстовых функций, таких как ЛЕВСИМВ, ПРАВСИМВ и ПСТР.
Например, чтобы получить последние 3 символа из A2 и вернуть результат в виде цифр, используйте следующее:
На приведенном ниже рисунке продемонстрирована формула трансформации:
Если вы не обернете функцию ПРАВСИМВ в ЗНАЧЕН, результат будет возвращен в виде набора символов, что делает невозможным любые вычисления с извлеченными значениями.
Этот метод подходит, когда вы точно знаете, сколько символов и откуда вы желаете получить, а затем превратить их в число.
Инструмент «текст по столбцам».
Это еще один способ использовать встроенные возможности Excel. При использовании для других целей, например для разделения ячеек, мастер «Текст по столбцам» представляет собой многоэтапный процесс. А вот чтобы просто выполнить нашу метаморфозу, нажимаете кнопку Готово на самом первом шаге
- Выберите позиции (можно и весь столбец), которые вы хотите конвертировать, и убедитесь, что их формат установлен на Общий.
- Перейдите на вкладку «Данные», группу «Инструменты данных» и нажмите кнопку «Текст по столбцам» .
- На шаге 1 мастера распределения выберите «Сразделителями» в разделе «Формат исходных данных и сразу чтобы завершить преобразование, нажмите «Готово» .
Это все, что нужно сделать!
Преобразование текста в число в ячейке Excel
При импорте файлов или копировании данных с числовыми значениями часто возникает проблема: число преобразуется в текст. В результате формулы не работают, вычисления становятся невозможными. Как это быстро исправить? Сначала посмотрим, как исправить ошибку без макросов.
Как преобразовать текст в число в Excel
Excel помогает пользователю сразу определить, значения в ячейках отформатированы как числа или как текст. Числовые форматы выравниваются по правому краю, текстовые – по левому.
Когда при импорте файлов или сбое в Excel числовой формат становится текстовым, в левом верхнем углу ячеек появляется зеленый треугольничек. Это знак ошибки. Ошибка также возникает, если перед числом поставить апостроф.
Способов преобразования текста в число существует несколько. Рассмотрим самые простые и удобные.
Использовать меню кнопки «Ошибка». При выделении любой ячейки с ошибкой слева появляется соответствующий значок. Это и есть кнопка «Ошибка». Если навести на нее курсор, появится знак раскрывающегося меню (черный треугольник). Выделяем столбец с числами в текстовом формате. Раскрываем меню кнопки «Ошибка». Нажимаем «Преобразовать в число». Применить любые математические действия. Подойдут простейшие операции, которые не изменяют результат (умножение / деление на единицу, прибавление / отнимание нуля, возведение в первую степень и т.д.). Добавить специальную вставку. Здесь также применяется простое арифметическое действие. Но вспомогательный столбец создавать не нужно. В отдельной ячейке написать цифру 1. Скопировать ячейку в буфер обмена (с помощью кнопки «Копировать» или сочетания клавиш Ctrl + C). Выделить столбец с редактируемыми числами. В контекстном меню кнопки «Вставить» нажать «Специальная вставка». В открывшемся окне установить галочку напротив «Умножить». После нажатия ОК текстовый формат преобразуется в числовой. Удаление непечатаемых символов. Иногда числовой формат не распознается программой из-за невидимых символов. Удалим их с помощью формулы, которую введем во вспомогательный столбец. Функция ПЕЧСИМВ удаляет непечатаемые знаки. СЖПРОБЕЛЫ – лишние пробелы. Функция ЗНАЧЕН преобразует текстовый формат в числовой. Применение инструмента «Текст по столбцам». Выделяем столбец с текстовыми аргументами, которые нужно преобразовать в числа. На вкладке «Данные» находим кнопку «Текст по столбцам». Откроется окно «Мастера». Нажимаем «Далее»
На третьем шаге обращаем внимание на формат данных столбца
Последний способ подходит в том случае, если значения находятся в одном столбце.
Макрос «Текст – число»
Преобразовать числа, сохраненные как текст, в числа можно с помощью макроса.
Есть набор значений, сохраненных в текстовом формате:
Чтобы вставить макрос, на вкладке «Разработчик» находим редактор Visual Basic. Открывается окно редактора. Для добавления кода нажимаем F7. Вставляем следующий код:
Чтобы он «заработал», нужно сохранить. Но книга Excel должна быть сохранена в формате с поддержкой макросов.
Теперь возвращаемся на страницу с цифрами. Выделяем столбец с данными. Нажимаем кнопку «Макросы». В открывшемся окне – список доступных для данной книги макросов. Выбираем нужный. Жмем «Выполнить».
Цифры переместились вправо.
Следовательно, значения в ячейках «стали» числами.
Если в столбце встречаются аргументы с определенным числом десятичных знаков (например, 3,45), можно использовать другой макрос.
Таким образом, возникающую при импорте или копировании числовых данных ошибку легко устранить. Преобразовать текст в число можно разными способами (с помощью макросов и без них). Сделать это просто и быстро. А впоследствии с числовыми аргументами производятся все необходимые операции.
Варианты решения
Существует несколько способов исправления ошибки формата. Будем рассматривать от простых методов к более сложным.
На заметку! В редакторе текстовая информация автоматически выравнивается по левой стороне, а числовые данные – по правой.
- Если ячейка содержит какую-то ошибку, программа сигнализирует об этом небольшим зеленым треугольником в верхнем левом углу. Нажимаете по нему, и выпадает список возможных действий. Выбираете строку преобразовать в число и получаете цифры вместо текста. Операцию повторяете для каждой ячейки с таким ярлычком.
- Создаете рядом столбец и проводите простейшую математическую операцию, которая не изменяет результат. Формула может содержать умножение или деление на единицу, а также сложение или вычитание нуля. Маркером автозаполнения применяете выражение ко всем значениям.
- Использование встроенных функций excel, которые помогают убрать пробелы, непечатные символы, а также преобразовывают текст в числовой формат. Функция ЗНАЧЕН является основой, а ПЕЧСИМВ и СЖПРОБЕЛЫ будут вспомогательными. Строка формул будет выглядеть следующим образом:
Массово применить сочетание функций можно при помощи автозаполнения.
- Последний способ удобно применять, если значения расположены в одной колонке. Используйте инструмент Текст по столбцам во вкладке Данные на Панели управления. Следуйте инструкциям мастера, а на последнем шаге укажите Общий формат ячеек. После нажатия кнопки Готово исходный столбец примет формат числа.
- Отдельно стоит упомянуть про возможности макросов в excel. Такой способ подходит для более опытных пользователей редактора, которые знают язык программирования Visual Basic. Можно написать программу самому, а можно в интернете найти готовые варианты. При этом макросы помогают решать не только текущую задачу, но и позволяют преобразовать число в текст. Обычно такой текст отображается прописью, что удобно для бухгалтерских и банковских документов. Более подробно об этом мы рассказывали в предыдущих статьях.
Как видите, существует несколько способов сделать из текста число. Ничего сложного в этой операции нет. Если нужны более серьезные преобразования, то стоит воспользоваться макросом.
Жми «Нравится» и получай только лучшие посты в Facebook ↓
Математические операции.
Другой способ — выполнить простую арифметическую операцию, которая фактически не изменяет исходное значение. В этом случае программа, если такая возможность существует, сама произведет необходимое преобразование.
Что бы это могло быть? Например, сложение с нулем, умножение или деление на 1.
= LA2 + 0
= LA2 * 1
= LA2 / 1
важно, чтобы эти действия не меняли значения чисел. Выше вы можете увидеть пример таких операций: двойное умножение на минус 1, умножение на 1, сложение на 0
Самый изящный и простой для ввода выглядит как «двойное отрицание»: перед ссылкой ставим два минуса, что есть, умножаем дважды на минус 1. Результат расчета не изменится, а написать такую формулу очень просто.
Однако, если исходные значения отформатированы как текст, Excel также может автоматически применить соответствующий формат к полученным результатам. Вы можете сказать это по их содержимому, выровненному по левому краю. Чтобы исправить это, убедитесь, что вы установили общий формат для ячеек, используемых в формуле.
Примечание. Если вы хотите, чтобы результаты были значениями, а не формулами, используйте Специальную вставку после применения этого метода, чтобы заменить их результатами.
Как конвертировать текст в числа в Excel
Конвертация текста в числа через меню ошибки
Если в левом верхнему углу ячеек с числовыми значениями появился зеленый треугольник, то:
- Выделите диапазон таких ячеек
- Кликните по иконке с желтым ромбом и восклицательным знаком
- В выпадающем списке выберите пункт меню “Преобразовать в число”
Как конвертировать текст в число с помощью смены формата ячейки
Есть еще один способ изменить формат значений текстовой ячейки на числовой. Сделать это можно с помощью смены формата ячейки на числовой. Как это сделать:
- Выделите левой клавишей мыши диапазон ячеек
- На панели инструментов перейдите на вкладку “Главная”
- Перейдите в подгруппу “Число” и в выпадающем списке выберите формат “Числовой”
Прочее. Программное обеспечение
Доброго времени, суток Вот макрос, который присваивает всем выделенным ячейкам формат «цифровой». Надеюсь я правильно понял что Вам было нужно. Я вставил набор чисел из word в excel и преобразовал в числа.
Сообщение от ibragimr: Вот макрос
где?Я бы тоже сделал как советуют в #2
Сообщение от ISN: Прибавьте «0» к ячейке в соседнем столбце и посмотрите на результат
Результат: к соседней ячейке прибавляется 0, больше ничего не происходит. upd: выделить проблемную ячейку, нажать ctrl+a, если надо — руками довыделять невыбранное — где-то рядом остается маркер устранения ошибки —> преобразовываем.
Сообщение от Studentroman: Выделить все нажать пкм выбрать формат ячеек выбрать числовой,не?
нет, это не делает число, распознаваемое екселем как текст, числом
Сообщение от engngr: Результат: к соседней ячейке прибавляется 0, больше ничего не происходит.
Не очень корректно написал, думал поймете смысл: в ячейке «А1» ваше число, тогда запишем в любой ячейке формулу «=А1+0», разумеется без кавычек.
а потом просто получившийся столбец переставить на место старого?
попробовал. в принципе рабочее решение, но для случая, когда каждое число в одной ячейке. У меня осложняется тем, что число находится в объединенных ячейках (см. скрин) И поэтому лишние нули.
Сообщение от eugenmax: а потом просто получившийся столбец переставить на место старого?
Да, а формулу можно будет удалить.
Сообщение от eugenmax: У меня осложняется тем, что число находится в объединенных ячейках (см. скрин)
Тогда только через макросы.
Сообщение от eugenmax: напишите после моего поста сообщение
Сообщение от CaMoCAD: если выделить n ячеек с зеленым треугольником (Ваш столбец F), то появится желтый ромб с воскл. знаком и стрелочкой.
Если есть пропущенные ячейки, то такой фокус не получится.
Сообщение от eugenmax: как бы разом их выделить и все преобразовать в число?
Все ответы правильные, только до конца не доделанные. Проблема решается так : 1. Выделяешь одну ячейку из всех проблемных так чтобы появился ромб 2. Нажимаешь Шифт и держишь, одновременно мышью кликаешь в верхний левый угол таблицы где пересекаются названия строк и столбцов. 3. Этим самым выделяются все ячейки таблицы. 4. Но одновременно с выделением в углу появляется ромб 5. Нажимаешь на ромб и выбираешь «Преобразовать в число» Всё. Во всех ячейках произойдёт преобразование.
Сообщение от maratovich: 5. Нажимаешь на ромб и выбираешь «Преобразовать в число»
какая функция макроса выполняет это действие?
Как преобразовать текст в число в Excel?
Бывает что, когда пытаешься провести вычисления с данными, формулы не работают или Excel выдает ошибку. Причины сразу могут быть неясны. Особенно часто проблема встречается после выгрузки из учетных систем, таких как 1С или SAP. Как избежать проблемы, когда формулы не работают? Чаще всего необходимо преобразовать текст в число в Excel? Несколько полезных способов, как бороться с этим несчастьем читайте ниже.
Очень часто числовые данные выгруженные из разных программ или из-за неправильных действий пользователя Excel воспринимается как текст, т.е. формулы возможно будут считаться неверно. Есть много способов преобразовать текст в число!
1. Преобразовать текст в число. Сделайте формат ячеек — число
С самого начала проверьте формат чисел. Выделите диапазон с данными (на нашем примере столбец B). Нажмите горячую клавишу ctrl + 1 (меню Формат ячеек если правой кнопкой мыши). На вкладке Число выберите Формат ячеек — Числовой.
Очень удобно пользоваться меню Число на главной панели. Здесь сразу доступны удобные форматы. Слева-направо: денежный, процентный, числовой + кнопки уменьшения/увеличения количества знаков после запятой. Рекомендую.
2. Исправьте ошибки — преобразуйте текст в число специальным инструментом
Первый шаг не помог? Не беда. Видите зеленые треугольники в верхних левых углах ячеек? Это значит что в ячейке что-то не так, Excel подсказывает Вам, что возможно там не число или формула отличается от соседних и т.п. Наведите на эту ячейку указатель, или выделите все эти ячейки и нажмите на появившийся слева значок.
В нашем случае Число сохранено как текст — кликните Преобразовать в число. Вуаля!
Это же доступно по адресу Формулы — Раздел Зависимости формул — Проверка формул
3. Умножьте все числа на 1
Не помог предыдущий вариант? Значит, ошибка довольно редкая, но решается просто. Есть крутая хитрость. В столбце С все значения столбца B перемножьте на 1. Да, буквально так, для ячейки C2 запишите =B2*1 и протяните вниз. Так Excel принудительно преобразовывает имеющиеся значения в число.
4. Удалите лишний символ в ячейках при помощи Найти и Заменить
И пункт 3 не помог? Значит в ячейках закрался какой-то символ, например как в ячейке В7 на первой картинке после первого числ стоит символ » «, это может быть даже не обычный пробел (поэтому скопируйте именно этот символ). Такими вещами очень часто грешит 1С, причем любой конфигурации.
Пользуемся инструментом Найти и заменить, подробное описание здесь . Если быстро нажимаете ctrl + H , появиться окно.
В поле Найти: вставляем скопированный символ. В поле Заменить на: ничего не ставим (т.е. заменяем на пусто). Жмем Заменить все.
5. Функция ЗНАЧЕН
И конечно, есть универсальный способ применить функцию =ЗНАЧЕН(), она лечит все ошибки с числами, кроме лишних символов (не касается пробелов) и преобразовывает текст в число. Очень удобно использовать ее для проверок в расчетах, как подстраховку от ошибок.
6. Перевод времени в число
На самом деле время и дата это уже число, но в нужном формате. Но в их обработке может быть сразу несколько хитростей. Читаем в этой статье.
P.S.
Кстати для того, чтобы убрать лишние пробелы в начале и конце ячеки с текстом (например при записи ФИО мы часто ошибаемся и ставим пробел в конце, который не видо). А так же, чтобы убрать двойные пробелы, есть классная функция =СЖПРОБЕЛЫ() . Она убирает все лишние пробелы!
Простой способ преобразования текста в дату без формул.
Как видите, преобразование текста в дату в Excel — далеко не тривиальная операция, выполняемая одним щелчком мыши. Если вас смущают достаточно сложные формулы, позвольте мне показать вам быстрый и простой способ.
Установите надстройку Ultimate Suite (бесплатную пробную версию можно скачать здесь), переключитесь на вкладку AblebitsTools (в ваш Excel будут добавлены 2 новые вкладки, содержащие более 60 очень полезных инструментов) и активируйте инструмент Text to Datе.
Чтобы преобразовать «дату-как-текст» в обычную дату, выполните следующие действия:
- Выделите ячейки с текстовыми данными и нажмите Text to Date .
- Укажите порядок дат (дни, месяцы и годы) в выбранных ячейках.
- Выберите, нужно ли включать время в преобразованные даты.
- Щелкните Преобразовать (Convert).
Вот и все! Результаты конвертации появятся в соседнем столбце, ваши исходные данные сохранятся. Если что-то пойдет не так, вы можете просто удалить их и повторить попытку с другим порядком дат.
А вот пример конвертации текста в дату и время:
Порядок действий тот же, но нужно не забыть снять галочку, чтобы не исключать время из расчётов.
Подсказка. Если вы выбрали преобразование даты и времени, но единицы времени отсутствуют в результатах, обязательно примените в ячейке формат, в котором отображаются как значения даты, так и времени. Дополнительные сведения см. в статье «Как создавать собственные форматы даты и времени» .
Если вам интересно узнать больше об этом замечательном инструменте, посетите его домашнюю страницу.
Итак, мы с вами рассмотрели как в Excel можно различными способами преобразовать текст в дату. Надеюсь, вам удалось найти метод по своему вкусу.
Я благодарю вас за чтение и надеюсь вновь увидеть вас на нашем сайте.
Также рекомендуем:
Преобразование даты в текст на практических примерах наглядно.
Преобразовать текст в число с помощью формулы
До сих пор мы обсуждали встроенные возможности, которые можно применить для перевода текста в число в Excel. Во многих ситуациях это может быть сделано быстрее с помощью формулы.
В Microsoft Excel есть специальная функция – ЗНАЧЕН (VALUE в английском варианте). Она обрабатывает как текст в кавычках, так и ссылку на элемент таблицы, содержащий символы для трансформирования.
Функция ЗНАЧЕН может даже распознавать набор цифр, включающих некоторые «лишние» символы.
Например, распознает цифры, записанные с разделителем тысяч в виде пробела:
Конвертируем число, введенное с символом валюты и разделителем тысяч:
Обе эти формулы возвратят число 1000.
Точно так же она расправляется с пробелами перед цифрами.
Чтобы преобразовать столбец символьных значений в числа, введите выражение в первую позицию и перетащите маркер заполнения, чтобы скопировать его вниз по столбцу.
Функция ЗНАЧЕН также пригодится, когда вы извлекаете что-либо из символьной строки с помощью одной из текстовых функций, таких как ЛЕВСИМВ, ПРАВСИМВ и ПСТР.
Например, чтобы получить последние 3 символа из A2 и вернуть результат в виде цифр, используйте следующее:
На приведенном ниже рисунке продемонстрирована формула трансформации:
Если вы не обернете функцию ПРАВСИМВ в ЗНАЧЕН, результат будет возвращен в виде набора символов, что делает невозможным любые вычисления с извлеченными значениями.
Этот метод подходит, когда вы точно знаете, сколько символов и откуда вы желаете получить, а затем превратить их в число.
Форматы отображения данных
Мастер преобразования текста в столбцы — не требующий формул способ преобразования текста в дату.
Как определить числа, записанные как текст?
В Excel есть встроенная функция проверки ошибок, которая предупреждает вас о возможных проблемах со значениями ячеек. Это выглядит как маленький зеленый треугольник в верхнем левом углу ячейки. При выборе ячейки с таким индикатором ошибки отображается предупреждающий знак с желтым восклицательным знаком (см. Скриншот ниже). Наведите указатель мыши на этот знак, и Excel сообщит вам о потенциальной проблеме: в этой ячейке число сохранено как текст или перед ним стоит апостроф .
В некоторых случаях индикатор ошибки не отображается для чисел, записанных в виде текста. Но есть и другие визуальные индикаторы текстовых чисел:
|
Число |
Строка (текстовое значение) |
На изображении ниже вы можете видеть текстовые представления чисел справа и реальные числа слева:
Есть несколько разных способов изменить текст на число Excel. Ниже мы рассмотрим их, начиная с самых быстрых и простых. Если простые методы не работают для вас, пожалуйста, не расстраивайтесь. Нет проблем, которые невозможно преодолеть. Просто нужно попробовать другие способы.