Как из текста вытащить цифры в excel

Как из текста вытащить цифры в excel

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949

ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071

Как найти число в произвольной части текста

Функция =ЧИСЛОИЗСТРОКИ(ТЕКСТ) извлекает число из текста ячейки. Причем не важно в какой части строки находится число: это может быть как начало или конец строки, так и ее середина. Функция имеет всего 1 аргумент:

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 содержит большой набор полезных функций, с помощью которых вы значительно сократите время и увеличите скорость работы с программой.

Извлекаем в EXCEL число из конца текстовой строки

Пусть текстовая строка Филатова123 находится в ячейке A1 . Чтобы извлечь число 123, расположенное справа, запишем формулу массива : =1*ПСТР(A1; ПОИСКПОЗ(ЛОЖЬ;ЕОШИБКА(1*ПСТР(A1;СТРОКА(ДВССЫЛ(«A1:A»&ДЛСТР(A1)));1));0); 255)

Если число расположено в начале или середине значения, то формула работать не будет (см. файл примера ).

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

О построении этой формулы смотрите здесь . Это также формула массива — не забудьте нажать CRTL+SHIFT+ENTER .

Несколько чисел в текстовой строке (через WORD)

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

• скопируйте столбец с текстовыми строками, содержащие числа, в MS WORD;
• нажмите CTRL+H , т.е. вызовите инструмент Найти и Заменить ;
• В поле Найти введите ^$ (любая буква) или его выберите из меню (см. рисунок ниже);

• в поле Заменить на: оставьте пустым (если все числа в строке нужно вывести одним числом, т.е. текстовая строка 123Филато11в6а будет преобразована в 123116 ) или введите пробел (если в дальнейшем потребуется вывести числа в отдельные ячейки);
• нажмите ОК, буквы будут заменены пробелами или просто убраны;
• скопируйте столбец обратно в MS EXCEL.

Если требуется вывести полученные числа в отдельные ячейки, то используйте инструмент Текст-по-столбцам (мастер текстов) или материал статьи Разнесение текстовых строк по столбцам.

Несколько чисел в текстовой строке (через формулы, все числа склеиваются в одно)

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

• подсчитайте количество букв в текстовой строке (ячейка А3 ) с помощью функции ДЛСТР() в ячейке B3 ;
• создайте табличку, состоящую из количества столбцов = количеству букв в текстовой строке;
• заголовкам столбцов присвойте порядковые номера ;
• в ячейку С3 введите формулу =ПСТР($A3;C$2;1) и протяните ее вправо, заполнив все столбцы.

Заменив формулу =ПСТР($A3;C$2;1) на =ЕСЛИ(ЕОШ(—ПСТР($A3;C$2;1));»»;—ПСТР($A3;C$2;1)) можно вывести только числовые значения. Собрать все числовые значения в одну ячейку можно с помощью формулы =—Т(C7&D7&E7&F7&G7&H7&I7&J7&K7&L7&M7&N7&O7&P7&Q7) или =СЦЕПИТЬ(C7;D7;E7;F7;G7;H7;I7;J7;K7;L7;M7;N7;O7;P7;Q7)+0

Несколько чисел в текстовой строке (через формулы, все числа размещаются в разные ячейки)

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

• как в предыдущем примере каждый символ текстовой строки выводим в отдельную ячейку;
• с помощью функций СЖПРОБЕЛЫ() и ( СЦЕПИТЬ() или аперсанда &) выводим текстовую строку без букв (т.е. только числа), между числами — 1 пробел;
• с помощью функции ПОИСК() находим начальные позиции каждого числа;
• с помощью функции ПСТР(), ЛЕВСИМВ(), ПРАВСИМВ() выводим числа в отдельные ячейки.

Решение из файла примера позволяет извлекать от 2-х до 4-х чисел из текстовых строк длиной до 15 символов. При желании решение можно легко расширить на большее количество символов и чисел.

Совет: В статье Извлекаем число из начала текстовой строки приведено решение соответствующей задачи. В статье Извлекаем число из середины текстовой строки приведено решение соответствующей задачи.

покупка

Как извлечь число только из текстовой строки в Excel?

Вы когда-нибудь хотели извлекать числа только из списка строк в Excel? Здесь я познакомлю вас с некоторыми способами быстрого и легкого извлечения только чисел в Excel.

Метод 1. Извлечь число только из текстовых строк с формулой

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

Выберите пустую ячейку, в которую вы хотите вывести извлеченное число, затем введите эту формулу: = СУММПРОИЗВ (СРЕДНЯЯ (0 & A5; НАИБОЛЬШИЙ (ИНДЕКС (- СРЕДНЕЕ (A5; СТРОКА (КОСВЕННАЯ («1:» & LEN (A5))); 1)) * СТРОКА (КОСВЕННАЯ («1:» & LEN (A5) )), 0), СТРОКА (КОСВЕННАЯ («1:» & LEN (A5)))) + 1, 1) * 10 ^ ROW (INDIRECT («1:» & LEN (A5))) / 10) , а затем перетащите маркер заполнения, чтобы заполнить диапазон, необходимый для применения этой формулы. Смотрите скриншот:

Ноты:

• 1. A5 стоит первые данные, из которых вы хотите извлечь числа только из списка.
• 2. Если в строке нет чисел, результат будет показан как 0.

Извлекать числа только из текстовых строк:

Работы С Нами Kutools for ExcelАвтора ВЫДЕРЖКИ функция, вы можете быстро извлекать только числа из ячеек текстовой строки. Нажмите, чтобы загрузить Kutools for Excel!

Метод 2: извлекать число только из текстовых строк с кодом VBA

Вот код VBA, который также может оказать вам услугу, пожалуйста, сделайте следующее:

1. Удерживайте Alt + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. Нажмите Вставить > Модулии вставьте следующий код в Модули Окно.

Код VBA: извлекать номер только из текстовой строки:

Sub ExtrNumbersFromRange() Dim xRg As Range Dim xDRg As Range Dim xRRg As Range Dim nCellLength As Integer Dim xNumber As Integer Dim strNumber As String Dim xTitleId As String Dim xI As Integer xTitleId = "KutoolsforExcel" Set xDRg = Application.InputBox("Please select text strings:", xTitleId, "", Type:=8) If TypeName(xDRg) = "Nothing" Then Exit Sub Set xRRg = Application.InputBox("Please select output cell:", xTitleId, "", Type:=8) If TypeName(xRRg) = "Nothing" Then Exit Sub xI = 0 strNumber = "" For Each xRg In xDRg xI = xI + 1 nCellLength = Len(xRg) For xNumber = 1 To nCellLength If IsNumeric(Mid(xRg, xNumber, 1)) Then strNumber = strNumber & Mid(xRg, xNumber, 1) End If Next xNumber xRRg.Item(xI) = strNumber strNumber = "" Next xRg End Sub 

3. А затем нажмите F5 нажмите клавишу для запуска этого кода, и появится окно подсказки, напоминающее о выборе текстового диапазона, который вы хотите использовать, см. снимок экрана:

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

5, Наконец, нажмите OK кнопку, и все числа в выбранных ячейках были извлечены сразу.

Метод 3: извлечь номер только из текстовой строки с помощью Kutools for Excel

Kutools for Excel также имеет мощную функцию, которая называется ВЫДЕРЖКИ, с помощью этой функции вы можете быстро извлечь только числа из исходных текстовых строк.

После установки Kutools for Excel, пожалуйста, сделайте следующее:

1. Щелкните ячейку помимо текстовой строки, в которую вы поместите результат, см. Снимок экрана:

2. Затем нажмите Кутулс > Функции Kutools > Текст > ВЫДЕРЖКИ, см. снимок экрана:

3. В Аргументы функций диалоговом окне выберите ячейку, в которой вы хотите извлечь числа из Текст текстовое поле, а затем введите правда or ложный в N текстовое поле, см. снимок экрана:

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

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

Method 4:Split text string into text and number columns individually with Kutools for Excel

If you want to split the text string into separated text and number columns, Kutools for Excel’s Split Cells also can help you to solve this task.

After installing Kutools for Excel , please do as follows:

1. Select the text string that you want to split, and then click Kutools > Text > Split Cells, see screenshot:

2. In the Split Cells dialog box, select Split to Columns under the Type section, and then check Text and number from the Split by section, see screenshot:

3. And then click Ok button, select a cell to put the result in the popped out dialog box, see screenshot:

4. Then click OK button, and the text strings have been split into separated text and number columns as following screenshot shown:

Метод 4: извлечь десятичное число только из текстовой строки с формулой

Если текстовые строки содержат некоторые десятичные числа на вашем листе, как вы могли бы извлечь из текстовых строк только десятичные числа?

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

Введите эту формулу ： =LOOKUP(9.9E+307,—LEFT(MID(A5,MIN(FIND(, $A5&»1023456789″)),999),ROW(INDIRECT(«1:999»)))) ，, А затем заполните дескриптор до ячеек, которые вы хотите содержать эту формулу, все десятичные числа были извлечены из текстовых строк, см. Снимок экрана:

Извлечь номер только из строк с помощью Kutools for Excel

Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!

Относительные статьи:

• Извлекать полужирный текст только в Excel
• Извлечь текст только в Excel