Как преобразовать текст в число в Excel

Обновлено: 04.07.2024

Часто можно найти числа, хранящиеся в виде текста в Excel. Это приводит к неправильным вычислениям при использовании этих ячеек в функциях Excel, таких как СУММ и СРЗНАЧ (поскольку эти функции игнорируют ячейки, содержащие текстовые значения). В таких случаях необходимо преобразовать ячейки, содержащие числа в виде текста, обратно в числа.

Теперь, прежде чем мы двинемся дальше, давайте сначала рассмотрим несколько причин, по которым вы можете получить книгу, в которой числа хранятся в виде текста.

Использование ‘ (апострофа) перед числом.
- Многие люди вводят апостроф перед числом, чтобы сделать его текстовым. Иногда это также происходит, когда вы загружаете данные из базы данных. Хотя это заставляет числа отображаться без апострофа, это влияет на ячейку, заставляя ее обрабатывать числа как текст.
Получение чисел в результате формулы (например, LEFT, RIGHT или MID)
- Если вы извлекаете числовую часть текстовой строки (или даже часть числа) с помощью функций ТЕКСТ, результатом будет число в текстовом формате.

Теперь давайте посмотрим, как действовать в таких случаях.

Преобразование текста в числа в Excel

В этом руководстве вы узнаете, как преобразовать текст в числа в Excel.

Метод, который вам нужно использовать, зависит от того, как число было преобразовано в текст. Вот те, которые рассматриваются в этом руководстве.

Используя параметр «Преобразовать в число».
Измените формат с Текст на Общий/Число.
Использование специальной вставки.
Использование текста в столбцах.
Использование комбинации функций VALUE, TRIM и CLEAN.

Преобразование текста в числа с помощью параметра «Преобразовать в число»

Когда к числу добавляется апостроф, формат числа изменяется на текстовый. В таких случаях вы заметите зеленый треугольник в верхней левой части ячейки.

В этом случае вы можете легко преобразовать числа в текст, выполнив следующие действия:

Это мгновенно преобразует все числа, сохраненные в виде текста, обратно в числа. Вы заметите, что после преобразования числа выравниваются по правому краю (в то время как при сохранении в виде текста они выравнивались по левому краю).

Преобразование текста в числа путем изменения формата ячейки

Когда числа отформатированы как текст, вы можете легко преобразовать их обратно в числа, изменив формат ячеек.

Вот шаги:

Выделите все ячейки, которые вы хотите преобразовать из текста в числа.
На главную –> Номер. В раскрывающемся списке "Числовой формат" выберите "Общий".

Это мгновенно изменит формат выбранных ячеек на Общий, а числа будут выровнены по правому краю. При желании вы можете выбрать любой из других форматов (например, числовой, валютный, бухгалтерский), что также приведет к тому, что значения в ячейках будут рассматриваться как числа.

Преобразование текста в числа с помощью специального параметра вставки

Чтобы преобразовать текст в числа с помощью параметра "Специальная вставка":

Преобразование текста в числа с использованием текста в столбец

Этот метод подходит в случаях, когда у вас есть данные в одном столбце.

Вот шаги:

Хотя результирующие ячейки по-прежнему могут быть в текстовом формате, а числа по-прежнему выровнены по левому краю, теперь это будет работать в таких функциях, как СУММ и СРЗНАЧ.

Преобразование текста в числа с помощью функции ЗНАЧ

Вы можете использовать комбинацию функций VALUE, TRIM и CLEAN для преобразования текста в числа.

Функция ЗНАЧ преобразует любой текст, представляющий число, обратно в число. удаляет все начальные и конечные пробелы.
Функция CLEAN удаляет лишние пробелы и непечатаемые символы, которые могут появиться, если вы импортируете данные или загружаете их из базы данных.

Предположим, вы хотите преобразовать ячейку A1 из текста в числа, вот формула:

Если вы хотите применить это и к другим ячейкам, вы можете скопировать и использовать формулу.

Вам также могут понравиться следующие учебные пособия по Excel:

БЕСПЛАТНАЯ КНИГА EXCEL

Получите электронную книгу «51 совет по Excel», чтобы резко повысить свою продуктивность и быстрее выполнять работу

7 мыслей о «Преобразование текста в числа в Excel — пошаговое руководство»

Я вижу, что большинство советов очень полезны

Спасибо за это. На самом деле это не сработало, но натолкнуло меня на мысль умножить ячейки на 1, перетащить и использовать вместо них те, которые сработали

У меня есть числа из финансовой базы данных, отформатированные с суффиксом для базисных пунктов, например, 14bp . По умолчанию они считываются в Excel как текст.Я не нашел способа преобразовать их в числа, за исключением очень трудоемких методов: использовать текст в столбцах, чтобы избавиться от bp (что я могу сделать при начальном анализе, когда у меня еще есть пробелы, чтобы я мог использовать фиксированную ширину синтаксический анализ, но если я попытаюсь сделать это после первого синтаксического анализа, то Excel удалит начальные пробелы, и мне придется использовать синтаксический анализ с разделителями и указать, чтобы он рассматривал «b» как разделитель.) В противном случае я ограничиваюсь использованием левого (thenumber, len(thenumber)-2), который затем может потребоваться снова преобразовать в текст.
Есть ли у вас более быстрый способ сделать это? Я подумал, возможно, если бы я сделал bp настраиваемым числовым форматом, Excel распознал бы 14bp как число при чтении, но это не работает.

спасибо. теперь я могу суммировать числа, меняя их формат с текстового на числовой

Одна из наиболее распространенных проблем, связанных с Excel, связана с форматированием чисел и текста в ячейках. Особенно раздражает, когда числа случайно вводятся в электронную таблицу в текстовом формате.

Когда это происходит, расчеты и различные формулы работают не совсем правильно или могут не работать вообще.

В этой статье вы узнаете, как определить, когда столбец или строка чисел на самом деле отформатированы как текст, и как преобразовать текст в числа, чтобы они снова работали в формулах и вычислениях. Это один из основных советов по работе с Excel, который должен знать каждый.

Являются ли данные ячейки текстом или числами?

Есть несколько способов узнать, отформатирован ли номер или набор чисел в столбце или строке как текст в Excel.

Самый простой способ — выделить ячейку, открыть меню "Главная" и в группе "Число" на ленте отметить формат числа, отображаемый в раскрывающемся списке.

Если в раскрывающемся списке отображается текст, это означает, что ячейка отформатирована как текстовый формат. Если вы хотите выполнить числовые вычисления в ячейке с помощью формул Excel, вам нужно сначала преобразовать ее.

Если кто-то ввел числа в текстовом формате, используя апостроф в ячейке, вы увидите маленький зеленый треугольник, указывающий, что значение было введено как текст.

Примечание. Если перед записью в ячейке стоит апостроф, форматирование ячейки становится текстовым.

Если вы обнаружили, используя любой из описанных выше подходов, что числовые данные вводятся в лист Excel в текстовом формате, вы можете использовать любой из приведенных ниже методов для преобразования этого текста в числа.

1. Преобразовать в число

Если вам нужно преобразовать данные, которые были введены в Excel с апострофом, вы можете легко преобразовать их обратно в числовой формат с помощью параметра «Преобразовать в число».

<р>1. Сначала выберите ячейки, которые вы хотите преобразовать обратно в числовой формат. Рядом с выделенным цветом появится желтый ромб с восклицательным знаком посередине.

<р>2. Выберите этот символ. В раскрывающемся списке выберите Преобразовать в число.

При этом все выбранные вами текстовые числа будут обновлены до общего числового формата данных.

Вы узнаете, что это сработало, когда все числа в вашем выделении переключятся с выравнивания по левому краю на выравнивание по правому краю в ячейках.

2. Использование текста в столбце

Еще один простой способ преобразовать текст в числа в Excel – это преобразовать сразу весь столбец значений. Это можно сделать с помощью функции «Текст в столбец».

<р>1. Выберите весь столбец данных, которые вы хотите преобразовать из текста в числа.

<р>2. Выберите «Данные» в меню, а затем выберите «Текст в столбцы» в разделе «Инструменты данных» на ленте.

<р>3. В окне мастера оставьте выбранным значение по умолчанию с разделителями и нажмите кнопку Далее.

<р>4. На следующей странице мастера оставьте выбранной вкладку по умолчанию и снова нажмите кнопку Далее.

<р>5.Наконец, на последней странице мастера убедитесь, что в разделе «Формат данных столбца» выбрано «Общие». Для поля «Назначение» вы можете либо выбрать новый столбец, в который вы хотите поместить числовые данные, либо просто сохранить текущий выбранный столбец как есть. Выберите Готово.

Теперь все ваши данные будут преобразованы в числовые значения, которые вы сможете использовать в формулах и расчетах Excel.

Примечание. Вы заметите, что фактическое форматирование ячейки не меняется с «Текст» на «Общий», даже несмотря на то, что сами значения теперь можно использовать как числа. Однако, если вы установите для выходного столбца новый столбец, вы заметите, что для форматирования нового столбца задано значение «Общее». Это лишь косметическая проблема, которая не влияет на поведение чисел в отформатированном столбце «Текст».

3. Изменение формата ячейки

Самый простой и быстрый способ преобразовать текст в числа в Excel — просто изменить формат ячейки в главном меню.

<р>1. Выберите все ячейки, которые вы хотите преобразовать. Вы можете выбрать весь столбец (не включая заголовок), если хотите преобразовать все ячейки в столбце.

<р>2. Выберите меню «Главная» и в группе «Число» на ленте выберите раскрывающийся список с текстом.

<р>3. Вы увидите список форматов на выбор. Выберите Общие, чтобы преобразовать в числовой формат. Или вы можете выбрать «Число», «Валюта», «Учет» или «Процент», если хотите, чтобы эти конкретные числовые форматы применялись к вашим числовым данным.

4. Использование вставляемых значений

Если вам нужно переместить текстовые ячейки, содержащие числа, в новую ячейку или столбец, вы можете использовать функцию "Специальная вставка".

<р>1. Выберите группу пустых ячеек, в которую вы хотите поместить вывод числовых данных. Выберите «Формат ячеек» во всплывающем меню.

<р>2. В открывшемся окне убедитесь, что в качестве числового формата выбран «Общий», и нажмите «ОК».

<р>3. Выделите весь столбец ячеек, который вы хотите преобразовать из текста в числа, щелкните правой кнопкой мыши и выберите "Копировать".

<р>4. Выберите первую ячейку в отформатированном пустом столбце, щелкните ячейку правой кнопкой мыши и выберите «Вставить значения». Вы увидите все числа в текстовом формате, вставленные в общий числовой формат.

Это работает, потому что когда вы выбираете «Вставить значения», вставляются только значения из исходной ячейки, а не исходное форматирование ячейки. Вместо этого используется форматирование ячейки назначения, которое вы настроили в первой части этого процесса.

5. Использование функции ЗНАЧ

В Excel есть специальная функция, которая преобразует число, отформатированное как текст, в числовое значение. Это функция ЗНАЧЕНИЕ.

Чтобы использовать эту функцию, выберите ячейку, в которую вы хотите поместить преобразованное число, и введите:

=ЗНАЧ(G2)

Замените «G2» выше на ячейку с числом, которое вы хотите преобразовать. Если вы конвертируете целый столбец чисел, начните только с первой ячейки.

Нажмите Enter, и вы увидите, что число в текстовом формате было преобразовано в число общего формата.

Затем вы можете заполнить оставшуюся часть пустого столбца до конца этого столбца, и формула ЗНАЧЕНИЕ также преобразует остальные ячейки в исходном столбце.

После использования любого из этих вариантов переформатирования чисел может потребоваться обновить данные ячейки после применения нового форматирования.

Как видите, существует несколько способов преобразования текста в числа в Excel. Вариант, который вы выберете, зависит только от того, где вы пытаетесь разместить вывод. Это также зависит от того, предпочитаете ли вы копирование и вставку, формулы Excel или параметры меню.

В конечном счете каждый из этих вариантов обеспечивает одинаковый конечный результат.

Райан пишет инструкции и другие статьи о технологиях в Интернете с 2007 года. Он имеет степень бакалавра наук в области электротехники, 13 лет работал в области автоматизации, 5 лет — в ИТ, а сейчас работает инженером по приложениям. Прочитать полную биографию Райана

Понравился ли вам этот совет? Если это так, загляните на наш канал YouTube на нашем родственном сайте Online Tech Tips.Мы охватываем Windows, Mac, программное обеспечение и приложения, а также предлагаем множество советов по устранению неполадок и обучающих видеороликов. Нажмите кнопку ниже, чтобы подписаться!

АВТОР АЛАН МЮРРЕЙ, ОБНОВЛЕНО 26 ИЮЛЯ 2021 Г. Чтение: 6 минут

Распространенное разочарование в Excel возникает, когда числа хранятся в виде текста.

Вы не сможете выполнять задачи Excel, такие как математические расчеты, создавать диаграммы на основе значений или группировать их в диапазоны, пока числа хранятся в виде текста.

В этом учебном пособии вы узнаете, как распознавать числа, сохраненные в виде текста, и несколько способов преобразования текста в числа.

Вот что мы рассмотрим:

1. Как проверить, является ли значение числовым или текстовым

Не всегда понятно, что число сохранено неправильно. Он может отображаться как число, но храниться как текст. Это вызывает путаницу у пользователей Excel.

Однако есть некоторые моменты, на которые следует обратить внимание, и способы их точной проверки.

Во-первых, Excel всегда будет отображать текст слева от ячейки, а числовые значения — справа от ячейки. Таким образом, если значение выровнено по левому краю, вероятно, это текст.

Это всего лишь подсказка, и она не гарантируется, поскольку пользователи могут изменить выравнивание значений ячеек. Тем не менее, это отличное место для начала и хорошая подсказка, на которую стоит обратить внимание.

Еще один способ проверки – выбрать диапазон значений и просмотреть расчеты в строке состояния.

Если значения все числовые, вы, вероятно, увидите расчеты среднего, количества и суммы. И если они являются текстовыми, выполняется только подсчет.

Опять же, эти расчеты могут быть изменены пользователями, так что это не идеальная проверка. Однако он будет работать в 95% случаев и является быстрым и простым способом проверки.

Чтобы быть полностью уверенным, мы можем использовать функцию Excel с именем ISNUMBER. Эта функция вернет значение TRUE, если значение является числовым, и FALSE, если оно не числовое.

Вам нужно только предоставить функции ISNUMBER значение для проверки. В этом примере в ячейку C2 была введена следующая формула.

Я преобразовал некоторые значения в числа. Таким образом, несмотря на то, что все они выглядят как числа, формула ЕЧИСЛО возвращает ЛОЖЬ, что упрощает определение проблемных значений.

2. Параметр Excel «Преобразовать в число»

Первый метод преобразования текста в числа, который мы рассмотрим, — это параметр «Преобразовать в число», доступный в Excel.

Это быстро и просто, но, к сожалению, не всегда доступно для вас.

Этот параметр доступен, когда числа сохраняются в виде текста в результате загрузки данных с веб-сайта или из какой-либо базы данных, либо могут быть скопированы и вставлены между программами.

Кроме того, возможно, кто-то случайно ввел апостроф перед номером. Это говорит Excel сохранить номер в виде текста, что очень полезно для ввода «фальшивых» номеров, таких как номера телефонов и идентификационные номера.

Зеленый треугольник отображается в левом верхнем углу ячейки, если этот параметр доступен для вас.

Этот треугольник означает, что значение не соответствует одному из правил проверки ошибок Excel и используется для выявления других ошибок, помимо простого распознавания чисел, сохраненных в виде текста.

Чтобы преобразовать в числа;

Выберите диапазон ячеек для изменения.
Нажмите значок восклицательного знака и выберите Преобразовать в число.

Значения преобразуются. Зеленый треугольник исчез, и теперь значения выровнены по правому краю. Вот как мы ожидаем увидеть числа.

3. Превратите текст в числа с помощью специальной вставки

Специальная вставка в Excel — потрясающий инструмент. Неудивительно, что он также может помочь нам преобразовать текст в числа.

Для этого нам нужно использовать Специальную вставку, чтобы выполнить простой расчет. Когда в расчетах используется текстовое значение, результат является числовым.

Введите цифру 1 в любую ячейку.
Скопируйте значение этой ячейки.
Выберите диапазон значений, которые необходимо преобразовать в числа.
Нажмите "Главная" > "Вставить стрелку списка" и "Специальная вставка".

<р>5. Выберите значения и операцию умножения. Нажмите "ОК".

4. Использование Text to Columns для преобразования текста

Текст в столбцы — еще один отличный способ быстро преобразовать текст в числа.

Выберите диапазон значений.
Нажмите "Данные" > "Текст в столбцы".
Шаг 1 мастера «Текст в столбцы» касается разделения текста между столбцами. Нам это не нужно, поэтому нажмите «Далее».

<р>4. На шаге 2 удалите все флажки-разделители.

<р>5. Шаг 3 спрашивает о форматировании значения. Оставьте его как «Общий» (параметр даты отлично подходит для работы со значениями даты). Нажмите Готово.

Возможно, вы не изменили параметр в процессе преобразования текста в столбцы, и мы могли нажать "Готово" перед шагом 3.

Однако вам, возможно, придется подумать, содержит ли ваше значение разделители, такие как пробелы или запятые.

Мне нравится этот метод преобразования текста в числа. Это быстро и просто.

5. Преобразование текста в числа с помощью формул

Предыдущие методы хороши, но требуют ручного вмешательства. Пользователю потребуется выполнить шаги для преобразования текста в числа.

С помощью формулы мы можем автоматизировать решение. Это не изменит значение в ячейке, содержащей текст, а создаст чистую версию в другой ячейке.

Если данные регулярно изменяются, формула гарантирует, что значения, подаваемые в сводную таблицу, диаграмму или другие формулы, по-прежнему будут числовыми. И никакого ручного вмешательства не требуется.

Использование формулы также отлично сочетается с другими функциями. Например, преобразование результатов текстовых функций в числа.

Это пример, который мы здесь рассмотрим.

В этом примере у нас есть данные, загруженные из источника, а значения в столбце B сохранены в виде текста из-за знака доллара.

Число успешно извлечено с помощью формулы. Однако функции ПРАВИЛЬНО и ДЛСТР, используемые в формуле, являются текстовыми функциями. Таким образом, результат возвращается в виде текста.

Алан – инструктор по Excel, YouTube-блогер и MVP Microsoft. Он помогает людям в Excel уже более 20 лет. Ему нравится обучение и радость, которую он получает от осознания того, что он облегчает трудовую жизнь людей. Алан ведет собственный блог Computergaga и является автором книги Advanced Excel Success. Он проводит ежемесячные встречи Excel в Лондоне, где люди учатся, делятся опытом и наслаждаются обществом друг друга. У Алана есть канал на YouTube с более чем 500 видео и более 24 миллионами просмотров. Алан регулярно публикует материалы на своем канале, чтобы делиться последними разработками и методами работы с Excel, на которые он натыкается.

Статьи по теме об аналитике данных

Что такое описательная аналитика? Полное руководство

Что такое диагностическая аналитика? Полное руководство

Как найти выбросы в данных с помощью Python (и как с ними справиться)

Что такое CareerFoundry?

CareerFoundry — это онлайн-школа для тех, кто хочет начать карьеру в сфере высоких технологий. Выберите программу, обратитесь к опытному наставнику и репетитору и станьте готовым к работе дизайнером, разработчиком или аналитиком с нуля или верните свои деньги.

Иногда значения на листах Excel выглядят как числа, но они не складываются, не умножаются и приводят к ошибкам в формулах. Распространенной причиной этого являются числа, отформатированные как текст. Во многих случаях Microsoft Excel достаточно умен, чтобы автоматически преобразовывать числовые строки, импортированные из других программ, в числа. Но иногда числа остаются в формате текста, что вызывает множество проблем в ваших электронных таблицах. В этом руководстве вы узнаете, как преобразовывать строки в «настоящие» числа.

Как определить числа, отформатированные как текст в Excel

Excel имеет встроенную функцию проверки ошибок, которая предупреждает вас о возможных проблемах со значениями ячеек. Это выглядит как маленький зеленый треугольник в верхнем левом углу ячейки. При выборе ячейки с индикатором ошибки отображается предупреждающий знак с желтым восклицательным знаком (см. скриншот ниже). Наведите указатель мыши на знак, и Excel сообщит вам о возможной проблеме: Число в этой ячейке отформатировано как текст или ему предшествует апостроф.

В некоторых случаях индикатор ошибки не отображается для чисел, отформатированных как текст. Но есть и другие визуальные индикаторы текста-цифры:

По умолчанию с выравниванием по правому краю.
Если выбрано несколько ячеек, в строке состояния отображается Среднее, Число и СУММА.

По умолчанию с выравниванием по левому краю.
Если выбрано несколько ячеек, в строке состояния отображается только Количество.
В поле Числовой формат отображается текстовый формат (во многих случаях, но не всегда).
В строке формул может быть виден начальный апостроф.

На изображении ниже вы можете видеть текстовое представление чисел справа и фактические числа слева:

Как преобразовать текст в число в Excel

Существует несколько различных способов изменить текст на число Excel. Ниже мы рассмотрим их все, начиная с самых быстрых и простых. Если простые методы не работают для вас, пожалуйста, не расстраивайтесь. Нет задачи, которую нельзя было бы преодолеть. Вам просто нужно попробовать другие способы.

Преобразование в число в Excel с проверкой ошибок

Если в ваших ячейках отображается индикатор ошибки (зеленый треугольник в левом верхнем углу), преобразование текстовых строк в числа выполняется двумя щелчками мыши:

Выделите все ячейки, содержащие числа в текстовом формате.
Нажмите на предупреждающий знак и выберите Преобразовать в число.

Готово!

Преобразовать текст в число, изменив формат ячейки

Еще один быстрый способ преобразования числовых значений, отформатированных как текст, в числа:

Выберите ячейки с числами в текстовом формате.
На вкладке Главная в группе Число выберите "Общие" или "Числа" в раскрывающемся списке Числовой формат.

Примечание. Этот метод не работает в некоторых сценариях. Например, если применить к ячейке текстовый формат, ввести число, а затем изменить формат ячейки на числовой, ячейка останется в текстовом формате.

Заменить текст на число с помощью специальной вставки

По сравнению с предыдущими методами, этот метод преобразования текста в число требует еще нескольких шагов, но работает почти в 100 % случаев.

Чтобы исправить числа, отформатированные как текст, с помощью Специальная вставка, выполните следующие действия:

Выберите ячейки с текстом и номером и установите для них формат «Общий», как описано выше.
Скопируйте пустую ячейку. Для этого либо выделите ячейку и нажмите Ctrl + C, либо щелкните правой кнопкой мыши и выберите Копировать в контекстном меню.
Выделите ячейки, которые нужно преобразовать в числа, щелкните правой кнопкой мыши и выберите "Специальная вставка". Либо нажмите сочетание клавиш Ctrl + Alt + V.
В диалоговом окне Специальная вставка выберите «Значения» в разделе Вставить и «Добавить» в разделе Операция.
Нажмите ОК.

Если все сделано правильно, ваши значения изменят выравнивание по умолчанию слева направо, то есть теперь Excel воспринимает их как числа.

Преобразование строки в число с текстом в столбцы

Это еще один способ преобразования текста в число без использования формул в Excel. При использовании для других целей, например для разделения ячеек, мастер «Текст в столбцы» представляет собой многоэтапный процесс. Чтобы выполнить преобразование текста в число, нажмите кнопку Готово на самом первом шаге :)

Выберите ячейки, которые вы хотите преобразовать в числа, и убедитесь, что для них установлен общий формат.
Перейдите на вкладку Данные, группу Инструменты данных и нажмите кнопку "Текст в столбцы".
На шаге 1 Мастера преобразования текста в столбцы выберите "Разделенные" в разделе Исходный тип данных и нажмите "Готово".

Это все!

Преобразование текста в число с помощью формулы

До сих пор мы обсуждали встроенные функции, которые можно использовать для преобразования текста в числа в Excel. Во многих случаях преобразование можно выполнить еще быстрее, используя формулу.

Формула 1. Преобразование строки в число в Excel

В Microsoft Excel есть специальная функция для преобразования строки в число — функция ЗНАЧ. Функция принимает как текстовую строку, заключенную в кавычки, так и ссылку на ячейку, содержащую преобразуемый текст.

Функция ЗНАЧ может даже распознать число, окруженное некоторыми "лишними" символами, чего не может сделать ни один из предыдущих методов.

Например, формула ЗНАЧ распознает число, введенное с символом валюты и разделителем тысяч:

Чтобы преобразовать столбец текстовых значений, введите формулу в первую ячейку и перетащите маркер заполнения, чтобы скопировать формулу вниз по столбцу:

Формула 2. Преобразование строки в дату

Помимо текстовых чисел, функция VALUE также может преобразовывать даты, представленные текстовыми строками.

Где A2 содержит текстовую дату.

По умолчанию формула ЗНАЧ возвращает порядковый номер, представляющий дату во внутренней системе Excel. Чтобы результат отображался как фактическая дата, вам просто нужно применить формат даты к ячейке формулы.

Того же результата можно добиться, используя функцию DATEVALUE:

Формула 3. Извлечение числа из строки

Функция ЗНАЧ также удобна, когда вы извлекаете число из текстовой строки с помощью одной из текстовых функций, таких как ЛЕВЫЙ, ПРАВЫЙ и СРЕДНИЙ.

Например, чтобы получить последние 3 символа из текстовой строки в формате A2 и вернуть результат в виде числа, используйте следующую формулу:

На снимке экрана ниже показана наша формула преобразования текста в число в действии:

Если вы не оберните функцию ПРАВИЛЬНО в ЗНАЧЕНИЕ, результат будет возвращен в виде текста, точнее числовой строки, что делает невозможным любые вычисления с извлеченными значениями.

Заменить строку Excel на число с помощью математических операций

Еще один простой способ преобразовать текстовое значение в число в Excel — выполнить простую арифметическую операцию, которая фактически не изменяет исходное значение. Что это может быть? Например, добавление нуля, умножение или деление на 1.

Если исходные значения отформатированы как текст, Excel может автоматически применить текстовый формат и к результатам. Вы можете заметить это по числам, выровненным по левому краю в ячейках формулы. Чтобы это исправить, обязательно установите Общий формат для ячеек с формулами.

Совет. Если вы хотите, чтобы результаты отображались в виде значений, а не формул, используйте функцию Специальная вставка, чтобы заменить формулы их значениями.

Вот как вы конвертируете текст в числа в Excel с помощью формул и встроенных функций. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

Вас также может заинтересовать

146 комментариев к статье "Как преобразовать текст в число в Excel"

Здравствуйте, я пытаюсь стереть пробел перед числом (для тысяч ячеек, которые я получил из другого файла), но не могу этого сделать:
Пример: ячейка содержит пробел перед числом 1, и мне нужно, чтобы это был просто номер 1, без пробела перед ним.
Проблема с числом 1 после пробела заключается в том, что Excel считает его текстом, его СУММА равна 0.
Когда я перезаписываю 1 в той ячейке, которая содержит пробел перед числом 1, это преобразуется в число, и SUM работает нормально; но я не могу сделать это клетка за клеткой для тысяч ячеек.
Пожалуйста, помогите мне.

Здравствуйте!
Вы пробовали способы, описанные в этом сообщении в блоге? Попробуйте Специальную вставку или Преобразовать в число, как описано в этой статье. Эти операции можно выполнять со всем диапазоном ячеек.

Уважаемый Александр, большое спасибо за добрый ответ.
Я попробовал Специальную вставку, но когда я скопировал ячейку, она скопировала только те, которые уже были числами, и не скопировала другие «числа», которые не работают как числа.

Когда я смотрю на числа в столбцах, я замечаю, что это же число находится немного (на один пробел) левее «так называемого числа» (которое нельзя добавить и т. д.).
Если я посмотрите, в частности, на эту ячейку, которая содержит, например, 145, у нее есть пробел перед числом,
но если я заменю это, написав над ней число 145, перед ним не будет пробела, и тогда он будет работать нормально как число.

Не могу понять, какой формат у этих так называемых чисел.

Привет!
Если я правильно понял, вы копируете ячейки со своими значениями, в которых числа написаны текстом. Внимательно прочитайте инструкции. Скопируйте только одну пустую ячейку, а затем используйте Специальную вставку.

Выполните «найти и заменить» и поместите пробел в поле поиска и ничего в поле замены. Однако это применимо ко всему.

Здравствуйте, Томми!
Попробуйте функцию TRIM. (TDLR, погуглите, как использовать TRIM)
Например, если столбец, содержащий числа, — это A, а первая ячейка с данными — это A2 [не считая строки заголовка], вы должны:
1. Создать (вставить новый столбец (в нашем примере B)
2 . В столбце B2 введите: TRIM (A2)
[это даст вывод содержимого ячейки A2 без каких-либо начальных или конечные пробелы]
3. Затем вы можете заполнить формулу, дважды щелкнув маркер заполнения или любым другим способом.
[это сделает весь столбец B таким же, как A без каких-либо начальных пробелов]
4. Затем вы можете скопировать столбец B и вставить его в A как ЗНАЧЕНИЯ, используя СПЕЦИАЛЬНУЮ ВСТАВКУ.

Надеюсь, это поможет.

Здравствуйте,
Я пытаюсь преобразовать опросы удовлетворенности из текста в числа, чтобы избежать ручного преобразования для получения большого количества опросов в неделю. Мне предоставили следующую формулу практически без информации о том, как добавить ее в экспортированный отчет. Существует основной лист, но данные необходимо добавить после завершения экспортированного листа. Представленная формула приведена ниже, и я пытаюсь преобразовать только один столбец из текста в числа.
=IF(T3489="Крайне недоволен",1,IF(T3489="В некоторой степени недоволен",2,IF(T3489="Ни доволен, ни недоволен",3,IF(T3489="В некоторой степени доволен",4 ,ЕСЛИ(T3489="Очень доволен",5,0)))))

Надеюсь услышать ответ!

Привет!
Я не совсем понимаю, чем могу помочь. Я не вижу ваших данных. Добавьте эту формулу на свой рабочий лист. При необходимости измените адрес ячейки.

Я думаю, что приведенная выше формула работает нормально и позволяет преобразовать столбец, содержащий ответ на опрос, в числа.В чем именно проблема, не могу понять из вашего сообщения выше.

14811,10
10363,44
1 059,00
-4 534,00
-10 104,90

Как представить это в цифрах? Текст в столбец не работает

Здравствуйте!
Попробуйте следующую формулу:

Если вы используете запятую в качестве десятичного разделителя, используйте эту формулу

Я бы порекомендовал вам воспользоваться нашим инструментом Преобразовать текст, который поможет вам преобразовывать числа, хранящиеся в виде текста, в числа. Он доступен как часть нашего Ultimate Suite for Excel, который вы можете установить в пробном режиме и бесплатно проверить, как он работает.

У меня есть список имен в Excel, который я хочу сформулировать в список чисел
Все имена разные, но я ищу их, чтобы преобразовать в числовой формат 1-8

Привет!
К сожалению, не видя ваших данных, трудно дать вам какой-либо совет. Пожалуйста, предоставьте мне пример исходных данных и ожидаемого результата.

Как преобразовать продолжительность в число:
пример:
0:07
0:15
0:35
И преобразовать их в чтение как:
0,07
0,15
0,35

Привет!
Пожалуйста, проверьте приведенную ниже формулу, она должна работать для вас:

У меня есть нечисловые данные (отвечал в опросе обезьян), ответы даны словами, т. е. согласны, полностью согласны, не согласны, полностью не согласны по многим вопросам. Я экспортировал данные опроса в таблицу Excel, и все ответы представлены в виде слова (строки). Мне нужно проанализировать базовую статистику, связав ее с одной переменной (например, респонденты мужского/женского пола). Как преобразовать эти строки в числовые для анализа в Excel?.

Здравствуйте!
Вы можете заменить слова числами с помощью встроенного в Excel инструмента поиска и замены.
Пожалуйста, ознакомьтесь с этой статьей: Как найти и заменить сразу несколько значений в Excel.
Надеюсь, эта информация окажется для вас полезной.

@Александр Трифунтов - Большое спасибо, это очень помогло и упростило работу. Еще раз большое спасибо.

Читайте также: