Функция XL.FindNumber: найти и извлечь число из текстовой строки
Функция XL.FindNumber – текстовый тип формулы – возвращает первое число (целое или десятичное), найденное в текстовой строке.
Перед началом работы добавьте «Функции» в Excel
«Функции» – это один из 20+ инструментов в составе надстройки XLTools для Excel. Работает в Excel 2019, 2016, 2013, 2010, десктоп Office 365.
Скачать XLTools для Excel
Преобразование чисел из текстового формата в числовой
Иногда числа форматируются и сохраняются в ячейках как текст, что впоследствии может привести к проблемам при вычислениях или нарушению порядка сортировки. Эта проблема может возникнуть при импорте или копировании данных из базы данных или другого внешнего источника данных.
Числа, отформатированные как текст, выравниваются в ячейках по левому, а не по правому краю, а также часто обозначаются индикатором ошибки.
В этой статье
- Способ 1. Преобразование чисел в текстовом формате с помощью функции проверки ошибок
- Способ 2. Преобразование чисел в текстовом формате с помощью функции «Специальная вставка»
- Способ 3. Применение числового формата к числам в текстовом формате
- Отключение проверки ошибок
Способ 1. Преобразование чисел в текстовом формате с помощью функции проверки ошибок
При импорте данных в Excel из другого источника, а также при вводе чисел в ячейки, которые ранее были отформатированы как текст, в левом верхнем углу ячейки можно заметить маленький зеленый треугольник. Этот индикатор ошибки указывает на то, что число хранится в текстовом виде, как показано в данном примере.
Если это нежелательно, выполните указанные ниже действия, чтобы преобразовать число в текстовом формате в обычное число.
-
Выделите любую ячейку или диапазон смежных ячеек с индикатором ошибки в верхнем левом углу . Выделение ячеек, диапазонов, строк и столбцов
| Чтобы выделить | Выполните следующие действия |
| Отдельную ячейку | Щелкните ячейку или воспользуйтесь клавишами со стрелками, чтобы перейти к нужной ячейке. |
| Диапазон ячеек | Щелкните первую ячейку диапазона, а затем перетащите указатель мыши на его последнюю ячейку. Или удерживая нажатой клавишу SHIFT, нажимайте клавиши со стрелками, чтобы расширить выделение. Кроме того, можно выделить первую ячейку диапазона, а затем нажать клавишу F8 для расширения выделения с помощью клавиш со стрелками. Чтобы остановить расширение выделенной области, еще раз нажмите клавишу F8. |
| Большой диапазон ячеек | Щелкните первую ячейку диапазона, а затем, удерживая нажатой клавишу SHIFT, щелкните последнюю ячейку диапазона. Чтобы перейти к последней ячейке, можно использовать полосу прокрутки. |
| Все ячейки листа | Нажмите кнопку Выделить все. |
Чтобы отменить выделение ячеек, щелкните любую ячейку на листе.
Нажмите появившуюся рядом с выделенной ячейкой или диапазоном ячеек кнопку ошибки.
Выберите в меню пункт Преобразовать в число. (Чтобы просто избавиться от индикатора ошибки без преобразования, выберите команду Пропустить ошибку.)
Эта команда преобразует числа из текстового формата обратно в числовой.
Способ 2. Преобразование чисел в текстовом формате с помощью функции «Специальная вставка»
При использовании этого способа каждая выделенная ячейка умножается на 1, чтобы принудительно преобразовать текст в обычное число. Поскольку содержимое ячейки умножается на 1, результат не меняется. Однако при этом приложение Excel фактически заменяет текст на эквивалентные числа.
-
Выделите пустую ячейку и убедитесь в том, что она представлена в числовом формате «Общий». Проверка числового формата
На вкладке Главная в группе Число нажмите стрелку в поле Числовой формат и выберите пункт Общий.
| Чтобы выделить | Выполните следующие действия |
| Отдельную ячейку | Щелкните ячейку или воспользуйтесь клавишами со стрелками, чтобы перейти к нужной ячейке. |
| Диапазон ячеек | Щелкните первую ячейку диапазона, а затем перетащите указатель мыши на его последнюю ячейку. Или удерживая нажатой клавишу SHIFT, нажимайте клавиши со стрелками, чтобы расширить выделение. Кроме того, можно выделить первую ячейку диапазона, а затем нажать клавишу F8 для расширения выделения с помощью клавиш со стрелками. Чтобы остановить расширение выделенной области, еще раз нажмите клавишу F8. |
| Большой диапазон ячеек | Щелкните первую ячейку диапазона, а затем, удерживая нажатой клавишу SHIFT, щелкните последнюю ячейку диапазона. Чтобы перейти к последней ячейке, можно использовать полосу прокрутки. |
| Все ячейки листа | Нажмите кнопку Выделить все. |
Некоторые программы бухгалтерского учета отображают отрицательные значения как текст со знаком минус (—) справа от значения. Чтобы преобразовать эти текстовые строки в значения, необходимо с помощью формулы извлечь все знаки текстовой строки кроме самого правого (знака минус) и умножить результат на -1.
Например, если в ячейке A2 содержится значение «156-«, приведенная ниже формула преобразует текст в значение «-156».
Способ 3. Применение числового формата к числам в текстовом формате
В некоторых случаях не нужно преобразовывать числа из текстового формата обратно в числовой, как было показано выше. Вместо этого можно просто применить числовой формат и получить тот же результат. Например, при вводе чисел в книгу и последующем форматировании этих чисел как текста в левом верхнем углу ячейки не появится зеленый индикатор ошибки. В этом случае можно применить числовой формат.
-
Выделите ячейки, которые содержат числа, сохраненные в виде текста. Выделение ячеек, диапазонов, строк и столбцов
| Чтобы выделить | Выполните следующие действия |
| Отдельную ячейку | Щелкните ячейку или воспользуйтесь клавишами со стрелками, чтобы перейти к нужной ячейке. |
| Диапазон ячеек | Щелкните первую ячейку диапазона, а затем перетащите указатель мыши на его последнюю ячейку. Или удерживая нажатой клавишу SHIFT, нажимайте клавиши со стрелками, чтобы расширить выделение. Кроме того, можно выделить первую ячейку диапазона, а затем нажать клавишу F8 для расширения выделения с помощью клавиш со стрелками. Чтобы остановить расширение выделенной области, еще раз нажмите клавишу F8. |
| Большой диапазон ячеек | Щелкните первую ячейку диапазона, а затем, удерживая нажатой клавишу SHIFT, щелкните последнюю ячейку диапазона. Чтобы перейти к последней ячейке, можно использовать полосу прокрутки. |
| Все ячейки листа | Нажмите кнопку Выделить все. |
Чтобы отменить выделение ячеек, щелкните любую ячейку на листе.
На вкладке Главная в группе Число нажмите кнопку вызова диалогового окна, расположенную рядом с надписью Число.
Отключение проверки ошибок
Если проверка ошибок в Excel включена, при вводе числа в ячейку с текстовым форматом отображается маленький зеленый треугольник. Если отображать индикаторы ошибок не требуется, их можно отключить.
- Откройте вкладку Файл.
- В группе Справка нажмите кнопку Параметры.
- В диалоговом окне Параметры Excel выберите категорию Формулы.
- Убедитесь, что в разделе Правила поиска ошибок установлен флажок Числа, отформатированные как текст или с предшествующим апострофом.
- Нажмите кнопку ОК.
Как из текста вытащить цифры в excel
То же самое для тех, кому важнее краткость:
=ПРОСМОТР(2^64;—ЛЕВСИМВ(ПСТР(A1&»_0″;МИН(НАЙТИ(;A1&»_0123456789″));15);СТРОКА($1:$15)))
Пользователь
Сообщений: 7 Регистрация: 01.01.1970
06.06.2010 06:17:43
Function ТОЛЬКОЦИФРЫ(ячейка As Range) As Long
Dim i As Integer, Результат As String
Dim Символ As String
For i = 1 To Len(ячейка)
Символ = Mid(ячейка, i, 1)
If IsNumeric(Символ) Then
Результат = Результат & Символ
End If
Next i
ТОЛЬКОЦИФРЫ = Val(Результат)
End Function
Пользователь
Сообщений: 7 Регистрация: 01.01.1970
06.06.2010 06:20:15
Пользователь
Сообщений: 4447 Регистрация: 23.12.2012
06.06.2010 06:25:15
ТОЛЬКОЦИФРЫ() — это пользовательская функция (макрос), который можно увидеть, если в post_129271.xls нажать Alt-F11
Пользователь
Сообщений: 4447 Регистрация: 23.12.2012
06.06.2010 06:28:38
Формула на английском:
=LOOKUP(2^64,—LEFT(MID(A1&»_0″,MIN(FIND(,A1&»_0123456789″)),15),))
Пользователь
Сообщений: 4447 Регистрация: 23.12.2012
06.06.2010 07:03:30
Приложил на всякий случай пример. Формула отобразится по-русски в русской версии Excel, по-английски — в английской и т.п.
Прикрепленные файлы
- post_129343.zip (2.48 КБ)
Пользователь
Сообщений: 3139 Регистрация: 23.12.2012
06.06.2010 17:11:07
Сколько вариантов!
Владимир, красиво, черт подери! Ну почему такие конструкции мне в голову не приходят? 🙁 Нужно работать над собой!
На пару символов короче:
=ПРОСМОТР(9^16;—ПСТР(ПСТР(A1&0;МИН(НАЙТИ(;A1&1234567890));15);1;СТРОКА($1:$15)))
Пользователь
Сообщений: 3139 Регистрация: 23.12.2012
06.06.2010 17:18:37
Хотя. зря убрал подчеркивание 🙂
Пользователь
Сообщений: 4447 Регистрация: 23.12.2012
06.06.2010 17:45:22
Владимир, красиво, черт подери! Ну почему такие конструкции мне в голову не приходят? 🙁 Нужно работать над собой!
На пару символов короче:
=ПРОСМОТР(9^16;—ПСТР(ПСТР(A1&0;МИН(НАЙТИ(;A1&1234567890));15);1;СТРОКА($1:$15)))
Ну да, Игорь, такой вариант выдаст лишний ноль, если число — в конце строки
т.е для ПИТЬЕВОЙ «ДАНОН» Персик-Маракуйя 290
выдаст 2900
У меня это тоже не сразу было отработано — см. выше 🙂
Как найти число в произвольной части текста
Функция =ЧИСЛОИЗСТРОКИ(ТЕКСТ) извлекает число из текста ячейки. Причем не важно в какой части строки находится число: это может быть как начало или конец строки, так и ее середина. Функция имеет всего 1 аргумент:
- ТЕКСТ — строка или ссылка на ячейку, из которой необходимо извлечь число.
Функция умеет распознавать целые и дробные числа. К примеру, результатом работы по извлечению числа из ячейки, которая содержит текст «Арбузы за 20,5 рублей за кг«, будет значение 20,5.
Для работы функции требуется установка надстройки VBA-Excel, после чего она будет добавлена в Excel и будет работает как и любая другая стандартная функция Excel.
Пример
На рисунке далее показаны результаты работы функции в различных случаях.
Внимание! Если текст ячейки содержит более одного числа, то функция их сольет воедино. Например, из текста «7 яблок и 3 груши» будет извлечено число 73.
Код на VBA
Function ЧИСЛОИЗСТРОКИ(СТРОКА As String) Dim sSymbol As String, sWord As String Dim i As Integer If СТРОКА = "" Then ЧИСЛОИЗСТРОКИ = "Н/Д": Exit Function sWord = "": sSymbol = "" ' Проходим по каждому символу For i = 1 To Len(СТРОКА) sSymbol = Mid(СТРОКА, i, 1) If LCase(sSymbol) Like "*[0-9.,;:-]*" Then If LCase(sSymbol) Like "*[.,]*" And i > 1 Then If Not Mid(СТРОКА, i - 1, 1) Like "*[0-9]*" Or Not Mid(СТРОКА, i + 1, 1) Like "*[0-9]*" Then sSymbol = "" End If End If sWord = sWord & sSymbol End If Next ЧИСЛОИЗСТРОКИ = sWord End Function
Надстройка
VBA-Excel
Надстройка для Excel содержит большой набор полезных функций, с помощью которых вы значительно сократите время и увеличите скорость работы с программой.