Какой формулой Excel можно выбрать из ячейки число?
Какой формулой извлечь из строки текста подстроку с цифрами?
Как в Экселе составить формулу чтобы выбрать из ячейки число?
Как составить формулу чтобы извлечь из текста лицевой счёт или сумму платежа?
Какая формула в Excel позволяет выбрать из ячейки число?
комментировать
в избранное бонус
vdtes t [37.2K]
3 года назад
Алгоритм формулы позволяющей выбрать из ячейки число такой:
- находим в тексте положение первой цифры (берём формулу из вопроса Как в Excel найти первую цифру ячейки?)
- Берём остаток строки с числом (функцией ПРАВСИМВ)
- и подставляем в формулу из вопроса Excel: как извлечь число если строка начинается с числа и содержит текст?
- Обрабатываем ошибку при отсутствии числа в ячейке (функция ЕСЛИОШИБКА)
=ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН(ПСТР(A1; СТРОКА( ДВССЫЛ( «1:»&ДЛСТР( A1))); 1))); 0)
остаток исходной строки
=ПРАВСИМВ(A1; ДЛСТР(A1)- ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН(ПСТР(A1; СТРОКА( ДВССЫЛ( «1:»&ДЛСТР( A1))); 1))); 0)+1)
Чтобы выбрать из ячейки число получится формула тип результата число:
=—ЛЕВСИМВ( ПРАВСИМВ(A1; ДЛСТР(A1)-ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН( ПСТР(A1; СТРОКА( ДВССЫЛ( «1:»&ДЛСТР( A1))); 1))); 0)+1); ПОИСКПОЗ(0; ЕЧИСЛО( —ПСТР( ПРАВСИМВ(A1;ДЛСТР(A1 )- ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН( ПСТР(A1; СТРОКА( ДВССЫЛ( «1:»&ДЛСТР( A1))); 1))); 0)+1); 1;СТРОКА( ДВССЫЛ( «1:»&ДЛСТР( ПРАВСИМВ(A1; ДЛСТР(A1) — ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН(ПСТР(A1; СТРОКА( ДВССЫЛ( «1:»&ДЛСТР( A1))); 1))); 0)+1) )))))*СТРОКА( ДВССЫЛ( «1:»&ДЛСТР(ПРАВСИМВ( A1; ДЛСТР(A1) — ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН(ПСТР(A1; СТРОКА( ДВССЫЛ( «1:»&ДЛСТР( A1))); 1))); 0)+1)) ));0)-1)
с учетом возможности обработки строк без цифр:
=ЕСЛИОШИБКА( —ЛЕВСИМВ( ПРАВСИМВ(A1; ДЛСТР(A1)-ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН( ПСТР(A1; СТРОКА( ДВССЫЛ( «1:»&ДЛСТР( A1))); 1))); 0)+1); ПОИСКПОЗ(0; ЕЧИСЛО( —ПСТР( ПРАВСИМВ(A1;ДЛСТР(A1 )- ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН( ПСТР(A1; СТРОКА( ДВССЫЛ( «1:»&ДЛСТР( A1))); 1))); 0)+1); 1;СТРОКА( ДВССЫЛ( «1:»&ДЛСТР( ПРАВСИМВ(A1; ДЛСТР(A1) — ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН(ПСТР(A1; СТРОКА( ДВССЫЛ( «1:»&ДЛСТР( A1))); 1))); 0)+1) )))))*СТРОКА( ДВССЫЛ( «1:»&ДЛСТР(ПРАВСИМВ( A1; ДЛСТР(A1) — ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН(ПСТР(A1; СТРОКА( ДВССЫЛ( «1:»&ДЛСТР( A1))); 1))); 0)+1)) ));0)-1); «нет цифр»)
Чтобы выбрать из ячейки число как текст надо убрать двойной минус(—):
=ЛЕВСИМВ( ПРАВСИМВ(A1; ДЛСТР(A1)-ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН( ПСТР(A1; СТРОКА( ДВССЫЛ( «1:»&ДЛСТР( A1))); 1))); 0)+1); ПОИСКПОЗ(0; ЕЧИСЛО( —ПСТР( ПРАВСИМВ(A1;ДЛСТР(A1 )- ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН( ПСТР(A1; СТРОКА( ДВССЫЛ( «1:»&ДЛСТР( A1))); 1))); 0)+1); 1;СТРОКА( ДВССЫЛ( «1:»&ДЛСТР( ПРАВСИМВ(A1; ДЛСТР(A1) — ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН(ПСТР(A1; СТРОКА( ДВССЫЛ( «1:»&ДЛСТР( A1))); 1))); 0)+1) )))))*СТРОКА( ДВССЫЛ( «1:»&ДЛСТР(ПРАВСИМВ( A1; ДЛСТР(A1) — ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН(ПСТР(A1; СТРОКА( ДВССЫЛ( «1:»&ДЛСТР( A1))); 1))); 0)+1)) ));0)-1)
с учетом возможности обработки строк без цифр:
=ЕСЛИОШИБКА( ЛЕВСИМВ( ПРАВСИМВ(A1; ДЛСТР(A1)-ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН( ПСТР(A1; СТРОКА( ДВССЫЛ( «1:»&ДЛСТР( A1))); 1))); 0)+1); ПОИСКПОЗ(0; ЕЧИСЛО( —ПСТР( ПРАВСИМВ(A1;ДЛСТР(A1 )- ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН( ПСТР(A1; СТРОКА( ДВССЫЛ( «1:»&ДЛСТР( A1))); 1))); 0)+1); 1;СТРОКА( ДВССЫЛ( «1:»&ДЛСТР( ПРАВСИМВ(A1; ДЛСТР(A1) — ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН(ПСТР(A1; СТРОКА( ДВССЫЛ( «1:»&ДЛСТР( A1))); 1))); 0)+1) )))))*СТРОКА( ДВССЫЛ( «1:»&ДЛСТР(ПРАВСИМВ( A1; ДЛСТР(A1) — ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН(ПСТР(A1; СТРОКА( ДВССЫЛ( «1:»&ДЛСТР( A1))); 1))); 0)+1)) ));0)-1); «нет цифр»)
Какой формулой выбрать число из ячейки Excel, если есть слова с цифрами?
Иногда в тексте ячеек могут встречаться слова, которые содержат цифры и буквы, в этом случае формулы из ответов на вопрос Какой формулой Excel можно выбрать из ячейки число? могут вернуть число, начинающиеся с первой цифры ячейки, а это может быть часть слова с цифрами, например обозначение модели.
В алгоритме необходимо учитывать, что число начинается с цифры, перед которой стоит определённый знак.
Возможны разные варианты расположения числа в тексте:
- После числа пробел.
- После числа определённый знак.
Сначала составим формулу для первого варианта:
Составим список знаков после которых следует число: : «-+;:()», этот список можно расширить исходя из особенностей конкретных текстов.
Excel позволяет обрабатывать текст как массив знаков, при использовании формулы массива (вводится через CTRL+Shift+Enter для версий меньше 2019)
Индекс знака в тексте (из ячейки A1) формируется выражением:
Каждый символ строки определяется выражением:
Проверку двойного условия:
символ является цифрой
Предшествующий символ находится в списке символов перед началом числа
Проверяем сложение двух условий для каждого символа массива и получаем следующее выражение для нахождения значения параметра начало_числа числа в строке:
=ПОИСКПОЗ(ИСТИНА;ЕЧИСЛО(—ПСТР( A1; СТРОКА( ДВССЫЛ( «1:»&ДЛСТР( A1 ))); 1)) + (ЕСЛИОШИБКА(—НАЙТИ( ПСТР(» «&A1; СТРОКА( ДВССЫЛ( «1:»&ДЛСТР( A1 ))); 1); » -+;:()»)>0; 0)) = 2; 0)
Положение символа после числа находим функцией
НАЙТИ(» «; A1; начало_числа+1)
Получили формулу для нахождения числа без знака подробнее про два минуса —:
=—ПСТР( A1; ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО(—ПСТР( A1; СТРОКА( ДВССЫЛ( «1:»&ДЛСТР( A1 ))); 1)) + (ЕСЛИОШИБКА( —НАЙТИ( ПСТР(» «&A1; СТРОКА( ДВССЫЛ( «1:»&ДЛСТР( A1 ))); 1); » -+;:()»)>0; 0)) = 2; 0); НАЙТИ(» «; ПСТР(A1&» «; ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО(—ПСТР( A1; СТРОКА( ДВССЫЛ( «1:»&ДЛСТР( A1 ))); 1)) + ( ЕСЛИОШИБКА( —НАЙТИ( ПСТР(» «&A1; СТРОКА( ДВССЫЛ( «1:»&ДЛСТР( A1 ))); 1); » -+;:()»)>0; 0))=2 ; 0); ДЛСТР( A1 )))-1)
Пример работы формулы:
Формула для нахождения числа с учётом знака:
Для учёта знака числа просто умножаем на -1 если перед числом стоит минус (начальные два минуса можно не писать)
=ПСТР( A1; ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО(—ПСТР( A1; СТРОКА( ДВССЫЛ( «1:»&ДЛСТР( A1 ))); 1)) + (ЕСЛИОШИБКА( —НАЙТИ( ПСТР(» «&A1; СТРОКА( ДВССЫЛ( «1:»&ДЛСТР( A1 ))); 1); » -+;:()»)>0; 0)) = 2; 0); НАЙТИ(» «; ПСТР(A1&» «; ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО(—ПСТР( A1; СТРОКА( ДВССЫЛ( «1:»&ДЛСТР( A1 ))); 1)) + ( ЕСЛИОШИБКА( —НАЙТИ( ПСТР(» «&A1; СТРОКА( ДВССЫЛ( «1:»&ДЛСТР( A1 ))); 1); » -+;:()»)>0; 0))=2; 0); ДЛСТР( A1 ))) — 1)*ЕСЛИ( ПСТР( A1; ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО(—ПСТР( A1; СТРОКА( ДВССЫЛ( «1:»&ДЛСТР( A1 ))); 1)) + (ЕСЛИОШИБКА( —НАЙТИ( ПСТР(» «&A1; СТРОКА( ДВССЫЛ( «1:»&ДЛСТР( A1 ))); 1); » -+;:()»)>0; 0)) = 2; 0)-1;1)=»-«;-1;1)
Для случая когда число может оканчиваться символом из заданного набора символов просто заменяем текст
На текст замены заданных символов на пробел
НАЙТИ(» «; ПОДСТАВИТЬ(ПОДСТАВИТЬ(. (ПОДСТАВИТЬ(A1;. ); «;»;»»). ;»)»; «»)
Для символов окончания числа » -+;:()» получим формулу для чисел без знака
=—ПСТР( A1; ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО(—ПСТР( A1; СТРОКА( ДВССЫЛ( «1:»&ДЛСТР( A1 ))); 1)) + (ЕСЛИОШИБКА( —НАЙТИ( ПСТР(» «&A1; СТРОКА( ДВССЫЛ( «1:»&ДЛСТР( A1 ))); 1); » -+;:()»)>0; 0)) = 2; 0); НАЙТИ(» «; ПСТР(ПОДСТАВИТЬ(ПОДСТАВИТЬ( ПОДСТАВИТЬ( ПОДСТАВИТЬ( ПОДСТАВИТЬ( ПОДСТАВИТЬ( A1&» «; «+»;» «); «-«;» «); «(«;» «); «)»;» «); «:»;» «); «;»;» «); ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО(—ПСТР( A1; СТРОКА( ДВССЫЛ( «1:»&ДЛСТР( A1 ))); 1)) + ( ЕСЛИОШИБКА( —НАЙТИ( ПСТР(» «&A1; СТРОКА( ДВССЫЛ( «1:»&ДЛСТР( A1 ))); 1); » -+;:()»)>0; 0))=2; 0); ДЛСТР( A1 )))-1)
для чисел со знаком
=ПСТР( A1; ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО(—ПСТР( A1; СТРОКА( ДВССЫЛ( «1:»&ДЛСТР( A1 ))); 1)) + (ЕСЛИОШИБКА( —НАЙТИ( ПСТР(» «&A1; СТРОКА( ДВССЫЛ( «1:»&ДЛСТР( A1 ))); 1); » -+;:()»)>0; 0)) = 2; 0); НАЙТИ(» «; ПСТР(ПОДСТАВИТЬ(ПОДСТАВИТЬ( ПОДСТАВИТЬ( ПОДСТАВИТЬ( ПОДСТАВИТЬ( ПОДСТАВИТЬ( A1&» «; «+»;» «); «-«;» «); «(«;» «); «)»;» «); «:»;» «); «;»;» «); ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО(—ПСТР( A1; СТРОКА( ДВССЫЛ( «1:»&ДЛСТР( A1 ))); 1)) + ( ЕСЛИОШИБКА( —НАЙТИ( ПСТР(» «&A1; СТРОКА( ДВССЫЛ( «1:»&ДЛСТР( A1 ))); 1); » -+;:()»)>0; 0))=2; 0); ДЛСТР( A1 )))-1)*ЕСЛИ(ПСТР( A1; ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО(—ПСТР( A1; СТРОКА( ДВССЫЛ( «1:»&ДЛСТР( A1 ))); 1)) + (ЕСЛИОШИБКА( —НАЙТИ( ПСТР(» «&A1; СТРОКА( ДВССЫЛ( «1:»&ДЛСТР( A1 ))); 1); » -+;:()»)>0; 0)) = 2; 0)-1;1)=»-«;-1;1)
Как извлечь число из текстовой строки в отдельную ячейку Excel
Очень жаль, но в Excel на данный момент нет функции, чтобы «достать» число из ячейки, в которой содержится текст.
Но, мы можем использовать комбинацию функций или Visual Basic для выполнения этой задачи.
Допустим, у нас есть обычная ячейка с текстом, в которой есть еще и число (пример на картинке ниже):
И нам необходимо это число записать в отдельной ячейке.
Узнайте Excel как свои пять пальцев на курсе по таблицам от Skillbox
Как извлечь число из текстовой строки в Excel 2016
Этот способ будет работать только в Excel 2016 и выше, потому что функция, которую мы будем использовать, была добавлена только в этой версии программы.
Мы будем использовать функцию ОБЪЕДИНИТЬ. Что она делает? Она извлекает все численные значения из строки. Например, у нас есть строка: «Витя купил мороженку за 100 рублей, а Вася за 20 рублей». Результатом выполнения функции будет число «10020».
=ОБЪЕДИНИТЬ("";ИСТИНА;ЕСЛИОШИБКА((ПСТР(A2;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A2)));1)*1);""))
Так как мы работаем с массивом, после написания формулы в ячейку нажмите CTRL + SHIFT + ENTER.
В случае, когда в строке нет ни одной цифры, результатом выполнения функции будет пустая строка.
Что функция делает?
Давайте разберем функцию по частям:
- СТРОКА(ДВССЫЛ(«1:»&ДЛСТР(A2))) — вычисление начнется с функции ДЛСТР, она вернет нам длину строки. Функция строка вернет массив чисел начиная с 1 и заканчивая тем числом, которое равняется длине нашей строки.
- (ПСТР(A2,СТРОКА(ДВССЫЛ(«1:»&ДЛСТР(A2)))),1)*1) — в этой части функции «отметаются» все текстовые значения, они все «падают» в ошибку #!ЗНАЧЕНИЕ. А численные значения остаются.
- ЕСЛИОШИБКА((ПСТР(A2,СТРОКА(ДВССЫЛ(«1:»&ДЛСТР(A2))),1)*1),»») — в этой части функции все ошибки, полученные ранее удаляются и остаются только численные значения.
- =ОБЪЕДИНИТЬ(«»;ИСТИНА;ЕСЛИОШИБКА((ПСТР(A2;СТРОКА(ДВССЫЛ(«1:»&ДЛСТР(A2)));1)*1);»»)) — и, наконец, все значения полученные ранее просто объединяются функцией ОБЪЕДИНИТЬ.
Важная информация: если у вас что-то не получается, вы можете проверить вывод отдельных частей функции с помощью горячей клавиши F2.
По аналогии мы можем получить только текстовое значение из строки (удаляя все числа). Формула функции будет такой:
=ОБЪЕДИНИТЬ("";ИСТИНА;ЕСЛИ(ЕОШИБКА(ПСТР(A2;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A2)));1)*1);ПСТР(A2;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A2)));1);""))))Разница между этими формулами в том, что функция ЕСЛИ проверяет ошибка ли полученное от ПСТР значение или нет.
Также, как и в прошлом варианте, функция ОБЪЕДИНИТЬ используется для объединения значений.
Важная информация: эта функция довольно сложная и требует некоторой вычислительной мощности. Поэтому когда вы используете эту функцию для обработки большого количества данных, имейте в виду, что это может занять некоторое время.
Как извлечь число из текстовой строки в Excel более ранних версий
Итак, в более ранних версиях Excel функции ОБЪЕДИНИТЬ еще нет. Поэтому придется использовать кое-что другое.
Допустим, у нас та же задача:
В таком случае, формула будет такой:
=ЕСЛИ(СУММ(ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(A2; , "")))>0; СУММПРОИЗВ(ПСТР(0&A2; НАИБОЛЬШИЙ(ИНДЕКС(ЕЧИСЛО(--ПСТР(A2;СТРОКА(ДВССЫЛ("$1:$"&ДЛСТР(A2)));1))* СТРОКА(ДВССЫЛ("$1:$"&ДЛСТР(A2)));0); СТРОКА(ДВССЫЛ("$1:$"&ДЛСТР(A2))))+1;1) * 10^СТРОКА(ДВССЫЛ("$1:$"&ДЛСТР(A2)))/10);"")Также, как и в Excel 2016 если функция не нашла чисел, результатом будет пустая строка.
Рекомендуем курс Excel по анализу данных от Skypro — очень глубокое и яркое погружение в Эксель.
Функция довольно сложная и «тяжелая» — поэтому, при работе с большими объемами данных, её выполнение может занять какое-то время.
Как разделить строку на текст и число с помощью Visual Basic
Как обычно, если вам нужно делать разделение строки очень часто, вы можете использовать для этого собственную функцию, которую мы создадим с помощью Visual Basic.
В конечном итоге, с помощью Visual Basic, мы получим одну маленькую функцию, которая будет делать все что вам нужно и не придется писать огромные формулы.
Итак, сейчас я продемонстрирую вам как создать две разные функции. Одна будет извлекать из строки только числа, а вторая только текст.
Извлекаем только число из текстовой строки используя Visual Basic
Итак, сейчас мы будем создавать свою собственную функцию для разделения строки на численные значения и текст.
Код Visual Basic:
Function GetNumeric(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1) Next i GetNumeric = Result End Function - Щелкните на «Разработчик»;
- Далее — «Visual Basic» (или горячие клавиши ALT + F11);
- В окошке Visual Basic правой кнопкой мышки на ваш лист;
- Далее «Insert» -> «Module»;
- В открывшееся окошко поместите наш код;
- Закройте Visual Basic;
Итак, мы создали функцию для получения всех чисел из текстовой строки и назвали её GetNumeric. Теперь давайте попробуем использовать её.
В результате её выполнения мы получили число из строки текста.
Не забывайте, что на данный момент нам необходимо сохранить файл Excel с соответствующим расширением, так как в нем используется Visual Basic макрос.
Также можно сохранить эту функцию в книгу макросов Excel. Таким образом, её использование будет еще быстрее и удобнее.
Извлекаем только текст из строки используя Visual Basic
А теперь давайте создадим функцию, которая будет получать только текст из строки(без чисел).
Код Visual Basic:
Function GetText(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If Not (IsNumeric(Mid(CellRef, i, 1))) Then Result = Result & Mid(CellRef, i, 1) Next i GetText = Result End Function- Щелкните на «Разработчик»;
- Откройте «Visual Basic»;
- В окошке Visual Basic нажмите правой кнопкой мышки на ваш лист;
- Далее «Insert» -> «Module»;
- И поместите наш код в открывшееся окошко;
- Закройте Visual Basic.
Эта функция будет разделять строку на текст и числа, а после возвращать нам в результате только текст.
Давайте проверим как она работает:
Узнайте Excel как свои пять пальцев на курсе по таблицам от Skillbox
Не забывайте, что на данный момент нам необходимо сохранить файл Excel с соответствующим расширением, так как в нем используется Visual Basic макрос.
Также можно сохранить эту функцию в книгу макросов Excel. Таким образом, её использование будет еще быстрее и удобнее.
Узнайте Excel как свои пять пальцев на курсе по таблицам от Skillbox
Как быстро извлечь число из текста в Excel
Проблема выделения числа из текста возникает достаточно часто, особенно когда вы работаете с данными, полученными из других программ. К примеру, нужно вытащить почтовый индекс из адреса, номенклатурный номер из строки с наименованием товара, номер счета из платежного документа. Нужное нам число может находиться в любом месте текста — в начале, в середине или в конце.
Вот что мы рассмотрим в этой статье:
Когда дело доходит до извлечения части текстового значения заданной длины, Эксель предоставляет три текстовых функции (ЛЕВСИМВ, ПРАВСИМВ и ПСТР) для быстрого выполнения этой задачи. А вот когда дело доходит до извлечения числа из буквенно-цифровой строки, Microsoft Excel … не предоставляет ничего.
Чтобы извлечь число из текста в Excel, требуется немного изобретательности, немного терпения и множество различных функций, вложенных друг в друга.
Или вы можете запустить инструмент «Извлечь (Extract)» из надстройки Ablebits Ultimate Suite и выполнить эту операцию одним щелчком мыши. Ниже вы найдете полную информацию обо всех этих методах.
Как извлечь число из конца текстовой строки.
Если у вас есть столбец буквенно-цифровых значений, в котором число всегда идет после текста, вы можете использовать одну из следующих формул, чтобы вытащить из них числа.
Важное замечание! В приведенных ниже формулах извлечение выполняется с помощью функций ПРАВСИМВ и ЛЕВСИМВ, которые относятся к категории текстовых функций. Эти функции всегда возвращают текст. В нашем случае результатом будет числовая подстрока, которая с точки зрения Excel также является текстом, а не числом. Если вам нужно, чтобы результат был числом (которое можно использовать в дальнейших вычислениях), оберните соответствующую формулу в функцию ЗНАЧЕН, или выполните с ней простейшую математическую операцию (например, двойное отрицание).
Чтобы извлечь число из строки «текстовое число», первое, что вам нужно знать, — это с какой позиции начать операцию. Итак, давайте определим положение первой цифры с помощью этой общего выражения:
О логике вычислений мы поговорим чуть позже. На данный момент просто замените слово «ячейка» ссылкой на позицию, содержащую исходный текст (в нашем случае A2), и запишите получившееся выражение в любую пустую клетку той же строки, скажем, в B2:
Хотя формула содержит константу массива, это обычное выражение, которое вводится обычным способом: нажатием клавиши Enter.
Как только позиция первой цифры определена, можно использовать функцию ПРАВСИМВ для извлечения числа. Чтобы узнать, сколько символов нужно извлечь, вы вычитаете позицию первой цифры из общей длины строки и добавляете единицу к результату, потому что первая цифра также должна быть включена:
Где A2 — исходная ячейка, а B2 — позиция первой цифры.
На следующем скриншоте показаны результаты:
Чтобы исключить вспомогательный столбец, содержащий позицию первой цифры, вы можете встроить формулу МИН непосредственно в функцию ПРАВСИМВ следующим образом:
Чтобы формула возвращала именно число, а не числовую строку, вложите ее в функцию ЗНАЧЕН:
Или просто примените двойное отрицание, использовав два знака «минус»:
Другой способ извлечь число из конца строки — использовать вот такое выражение:
Используя исходный текст в A2, вы записываете приведенную ниже формулу в B2 или любую другую пустую ячейку в той же строке, а затем копируете её вниз по столбцу:
Примечание. Эти формулы предназначены для случая, когда числа находятся только в конце текстовой строки. Если некоторые цифры также находятся в середине или в начале, то ничего не будет работать.
Этих недостатков не имеет третья формула, которая извлекает только последнее число в тексте, игнорируя все предыдущие:
=ПРАВСИМВ(A2; ДЛСТР(A2) — МАКС(ЕСЛИ(ЕЧИСЛО(ПСТР(A2; СТРОКА(ДВССЫЛ( «1:»&ДЛСТР(A2))); 1) *1)=ЛОЖЬ; СТРОКА(ДВССЫЛ( «1:»&ДЛСТР(A2))); 0)))
На скриншоте ниже вы видите результат ее работы.
Как видите, цифры в начале или в середине текста игнорируются. Также обратите внимание, что результатом, как и в предыдущих формулах, является число, записанное в виде текста. Как превратить его в нормальное число, мы уже рассмотрели выше в этой статье.
Примечание. Если вы используете Excel 2019 или более ранние версии, нужно использовать формулу массива, нажав при вводе комбинацию Ctrl+Shift+Enter . Если у вас Office365, вводите как обычно, через Enter .
Как извлечь число из начала текстовой строки
Если вы работаете со строками, в которых текст находится после числа, решение для извлечения числа будет аналогично описанному выше. С той только разницей, что вы используете функцию ЛЕВСИМВ для извлечения из левой части текста:
Используя этот метод для A2, извлекаем число при помощи такого выражения:
Это решение работает для текстовых выражений, которые содержат числа только в начале. Если некоторые цифры также находятся в середине или в конце строки, формула не будет работать.
Если вы хотите извлечь только числа слева и игнорировать остальные, воспользуйтесь другой формулой:
Или чуть модифицируем, чтобы ускорить расчеты:
Если у вас Excel 2019 и ниже, вводите ее как формулу массива, используя Ctrl+Shift+Enter . В Office365 и выше можно вводить как обычно.
Примечание. Как и в случае с функцией ПРАВСИМВ, функция ЛЕВСИМВ также возвращает числовую подстроку, которая технически является текстом, а не числом.
Как получить число из любой позиции в тексте
Если ваша задача подразумевает извлечение числа из любого места строки, вы можете использовать следующую формулу:
=СУММПРОИЗВ(ПСТР(0&A2; НАИБОЛЬШИЙ(ИНДЕКС(ЕЧИСЛО(—ПСТР(A2; СТРОКА(ДВССЫЛ(«1:»&ДЛСТР(A2))); 1)) * СТРОКА(ДВССЫЛ(«1:»&ДЛСТР(A2))); 0); СТРОКА(ДВССЫЛ(«1:»&ДЛСТР(A2))))+1; 1) * 10^СТРОКА(ДВССЫЛ(«1:»&ДЛСТР(A2)))/10)
Где A2 — исходная текстовая строка.
Для пояснения, как это работает, потребуется отдельная статья. Поэтому вы можете просто скопировать на свой рабочий лист, чтобы убедиться, что это действительно работает 🙂
Обратите внимание, что в этом случае в тексте могут находиться несколько чисел. Все они будут извлечены и объединены в единое целое.
Однако, изучив результаты, вы можете заметить один незначительный недостаток: если исходный текст в ячейке не содержит числа, формула возвращает ноль, как в строке 7 на скриншоте выше. Чтобы исправить это, вы можете заключить формулу в оператор ЕСЛИ, который проверит, содержит ли исходный текст какое-либо число. Если это так, формула извлекает это число, в противном случае возвращает пустую строку:
В отличие от всех предыдущих примеров, результатом этих формул является число. Чтобы убедиться в этом, просто обратите внимание на выровненные по правому краю значения в столбце B и усеченные ведущие нули (например, 88 вместо 088).
Если число, которое вы хотите извлечь, ограничено какими-то знаками-разделителями, то можно использовать функцию ПСТР. Рассмотрим пример, как получить номер счета из текста платежа.
Мы будем искать позицию знака «№» и позицию следующего за ним первого пробела. То, что находится между ними, как раз и будет номером счёта:
На скриншоте ниже вы видите, как это работает.
Вот еще один возможный вариант вынимания числа из текста. Необходимо извлечь первое встретившееся число из текста.
или заменяем список цифр функцией:
Как видите, получаем только первое число, независимо от его расположения:
И еще один пример. Давайте попробуем достать все числа из текста, разграничив их каким-то разделителем. Например, дефисом “-“.
В этом случае придется использовать формулу массива:
Мы нашли все числа в тексте, как вы видите на скриншоте ниже:
Откорректировав эту формулу, вы можете использовать любой другой разделитель.
Поскольку между ними есть разделители, то вы легко можете распределить эти числа в отдельные ячейки. Как это сделать — читайте в материале 8 способов разделить ячейку Excel на две или несколько.
Как выделить число из текста с помощью Ultimate Suite
Как вы только что видели, не существует простой и понятной формулы Excel для извлечения чисел из текстовой строки. Если у вас есть трудности с пониманием формул или их настройкой для ваших наборов данных, вам может понравиться этот простой способ получить число из текста в Excel.
Ultimate Suite, добавленной на вашу ленту Excel, вы можете быстро получить число из любой буквенно-цифровой строки:
- Перейдите на вкладку AblebitsData >Text и нажмите Извлечь (Extract) :
- Выделите все ячейки с данными, которые нужно обработать.
- На панели инструмента «Извлечь (Extract)» установите переключатель «Извлечь числа (Extract numbers)».
- В зависимости от того, хотите ли вы, чтобы результаты были формулами или значениями, выберите поле «Вставить как формулу (Insert as formula)» или оставьте его пустым (по умолчанию).
Я советую активировать это поле, если вы хотите, чтобы извлеченные числа обновлялись автоматически, как только в исходные значения вносятся какие-либо изменения. Если нужно, чтобы результаты не зависели от будущих изменений (например, если вы планируете удалить исходные данные позже), не используйте эту опцию.
- Нажмите кнопку «Вставить результаты (Insert Results)». Готово!
Как и в предыдущем примере, результаты извлечения являются числами. Это означает, что вы можете подсчитывать, суммировать, усреднять или выполнять любые другие вычисления с ними.
скачайте пробную версию Ultimate Suite и убедитесь сами 🙂
Если вы хотите иметь это, а также еще более 60 полезных инструментов в Excel, воспользуйтесь этой надстройкой.
Я постарался дать вам максимально полные рекомендации, какими способами можно извлечь число из текста. Конечно, они не могут охватить все возможные случаи. Поэтому если встретилось что-то особенно заковыристое — не стесняйтесь писать в комментариях. Постараюсь помочь по мере сил.