Excel: как извлечь число если строка начинается с числа и содержит текст?
В таблице числа записаны вместе с текстом: сначала число потом текст.
Какой формулой в Эксель преобразовать строку в число, если в ячейке сначала записано число а за ним следует текст.
Как извлечь из строки число, если строка содержит всего одно число, а далее следует текст.
1 ответ:
Чтобы выбрать число из ячейки применим такой алгоритм:
с помощью формулы массива найдем последнюю цифру в строке (далее идет текст) и возьём начало строки по последнюю цифру включительно.
Если числа записаны в колонке C3 то извлечь число можно формулой:
=ЗНАЧЕН( ЛЕВСИМВ( C3; МАКС( ЕЧИСЛО( ЗНАЧЕН( ПСТР( C3; СТРОКА( ДВССЫЛ( "1:"&ДЛСТР( C3 ))); 1)))*СТРОКА( ДВССЫЛ( "1:"&ДЛСТР(C3) )))))
ВНИМАНИЕ: формулу нужно вводить как формулу массива ограничивая ввод комбинацией клавиш CTRL+SHIFT+ENTER
эту же формулу можно записать немножко короче:
=--ЛЕВСИМВ( C3; МАКС( ЕЧИСЛО( --ПСТР( C3; СТРОКА( ДВССЫЛ( "1:"&ДЛСТР( C3 ))); 1))*СТРОКА( ДВССЫЛ( "1:"&ДЛСТР(C3) ))))
Эту формулу надо растянуть на весь диапазон.
Возможно при копировании формулы с сайта "Большой Вопрос" в свой проект придется заменить адреса ячеек (либо вручную либо через сервис замены адресов ячеек в формулах эксель )
чтобы сделать формулу более понятной можно поделить на строки см Как сделать понятной и читаемой огромную формулу Excel?
Читайте также
Допустим, что исходный текст записан в ячейке B3
Сначала надо проверить есть ли в начале текста артикул или условное цифровое обозначение, номер или код.
если текст начинается с цифры, то артикул есть
Формула для проверки (если есть артикул то результат ИСТИНА):
=ЕЧИСЛО( ЗНАЧЕН( ПСТР(B3;1;1) ))
Формула для столбца с цифрами:
=ЕСЛИ( ЕЧИСЛО( ЗНАЧЕН( ПСТР(B3;1;1) )) ; ЛЕВСИМВ( B3; НАЙТИ(" "; B3)) ; "")
Формула для столбца с наименованием товара:
=ЕСЛИ( ЕЧИСЛО( ЗНАЧЕН( ПСТР( B3;1;1) )) ; ПРАВСИМВ( B3 ; ДЛСТР(B3)-НАЙТИ( " ";B3)) ; B3)
После копирования этих формул в свою таблицу надо заменить ячейку B3 на ту, в которой содержится текст, а потом растянуть на весь диапазон значений.
Под числом можно считать подстроку, которая начинается с цифры и не может начинаться с 0, поэтому подсчитать количество чисел в строке можно как количество подстрок, которые начинаются с цифры от 1 до 9 перед цифрой стоит разделитель подстрок - пробел.
Сначала добавим перед каждым числом символ, которого нет в строке, например символ с кодом 5, тогда количество чисел будет равно разнице длин получившейся строки и исходной.
Чтобы исключить ошибку если строка начинается с числа, к исходной строке надо добавить пробел с начала строки.
Пример
Исходная строка находится в ячейке A2
Формула для подсчета количества чисел в тексте ячейки примет вид:
=ДЛСТР(ПОДСТАВИТЬ(ПО<wbr />ДСТАВИТЬ(ПОДСТАВИТЬ(П<wbr />ОДСТАВИТЬ(ПОДСТАВИТЬ(<wbr />ПОДСТАВИТЬ(ПОДСТАВИТЬ<wbr />(ПОДСТАВИТЬ(ПОДСТАВИТ<wbr />Ь(" "&A2;" 1";СИМВОЛ(5)&" 1");" 2";СИМВОЛ(5)&" 2");" 3";СИМВОЛ(5)&" 3");" 4";СИМВОЛ(5)&"4");" 5";СИМВОЛ(5)&" 5");" 6";СИМВОЛ(5)&" 6");" 7";СИМВОЛ(5)&" 7");" 8";СИМВОЛ(5)&" 8");" 9";СИМВОЛ(5)&" 9"))-ДЛСТР(" "&A2)
