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

Как

КАдам Дэвидсон

Опубликовано7 июня 2023 г.

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

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

Как извлечь число из конца строки с помощью Flash Fill

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

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

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

1. Выберите ячейку рядом с первой ячейкой, содержащей ваши строки.
   
2. Введите число, которое появляется в конце строки.
   
3. Если все ваши данные находятся в непрерывном столбце без пробелов, выберите ячейку непосредственно под той, в которую вы только что ввели число.
   
4. Если в некоторых строках ваших данных есть пробелы, выберите первую ячейку и все ячейки ниже, из которых вы хотите извлечь числа, включая все пустые строки.
   
5. Нажимать Ctrl+Е в Windows или Ctrl+E на Mac, чтобы запустить функцию Flash Fill.
6. Ваши оставшиеся ячейки будут автоматически заполнены на основе шаблона, обнаруженного Excel. В большинстве случаев числа с конца строки будут извлечены правильно.
   

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

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

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

1. Нажмите в ячейке, где вы хотите, чтобы извлеченный номер появился.
2. Введите следующую формулу, заменив «B2» на ячейку, содержащую вашу строку:

   =ЗНАЧ(ПРАВО(B2,ДЛСТР(B2)-МАКС(ЕСЛИ(ЕСЧИСЛО(СРЕДН(B2,СТРОКА(ДВССЫЛ("1:"&ДЛСТР(B2))),1)*1)=ЛОЖЬ, СТРОКА(ДВССЫЛ(" 1:"&ДЛСТР(В2))),0))))

3. Нажимать Входить.
4. Число должно быть извлечено из строки.
5. Чтобы применить формулу ко всем оставшимся ячейкам, щелкните маркер перетаскивания в правом нижнем углу ячейки.
   
6. Перетащите вниз по ячейкам, в которые вы хотите реплицировать формулу.
   
7. Отпустите кнопку мыши, и формула будет скопирована в выбранные ячейки.
8. Эти ячейки теперь должны содержать ваши извлеченные числа.
   
9. Любые ячейки без строки для извлечения вернут ошибку. Если вы хотите вместо этого вернуть пустую ячейку, вы можете вместо этого использовать формулу:

   =ЕСЛИОШИБКА(ЗНАЧЕНИЕ(ПРАВО(B2,ДЛСТР(B2)-МАКС(ЕСЛИ(ЕСЧИСЛО(СРЕДН(B2,СТРОКА(ДВССЫЛ("1:"&ДЛСТР(B2))),1)*1)=ЛОЖЬ, СТРОКА(ДВССЫЛ ("1:"&ДЛСТР(B2))),0)))),"")

10. Любые пустые ячейки теперь будут возвращать пустое место.
    

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

Например, AB5HG23 преобразуется в 1, 2, 0, 4, 5, 0, 0. Затем он ищет в этом списке наибольшее значение, которое является позицией последнего символа, не являющегося числом. Наконец, он возвращает все символы после этой точки, то есть числа в конце строки. Затем функция ЗНАЧ преобразует это обратно в число. Просто, верно?

Как извлечь число из начала строки с помощью Flash Fill

Вы также можете использовать Flash Fill для извлечения числа из начала строки, заполнив первый пример самостоятельно и позволив Excel остановить шаблон.

Чтобы извлечь число из начала строки с помощью Flash Fill:

1. Выберите ячейку справа от первой строки с числом, которое вы хотите извлечь.
2. Введите число, которое появляется в начале строки.
   
3. Если все ваши данные находятся в непрерывном столбце без пробелов, выберите ячейку непосредственно под ним.
   
4. Если в ваших данных есть пробелы, выберите первую ячейку и все ячейки ниже, из которых вы хотите извлечь числа, включая все пустые строки.
   
5. Нажимать Ctrl+Е в Windows или Ctrl+E на Mac, чтобы запустить Flash Fill.
6. В выбранных вами ячейках теперь должны отображаться извлеченные числа.
   

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

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

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

1. Выберите ячейку, в которой вы хотите, чтобы извлеченный номер появился.
2. Введите следующую формулу, заменив «B18» на ячейку, содержащую вашу строку:

   = ЗНАЧЕНИЕ (ЛЕВО (B18, СОВПАДЕНИЕ (ЛОЖЬ, ISNUMBER (СРЕДНЯЯ (B18, СТРОКА (ДВССЫЛ ("1:"&ДЛСТР(B18)+1)),1)*1),0)-1))

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

   =ЕСЛИОШИБКА(ЗНАЧЕНИЕ(ЛЕВО(B18,СООТВЕТСТВИЕ(ЛОЖЬ, IНОМЕР(СРЕДНИЙ(B18,СТРОКА(ДВССЫЛ("1:"&ДЛСТР(B18)+1)),1)*1),0)-1))," ")

Как извлечь числа из любого места в строке с помощью Flash Fill

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

Чтобы извлечь числа из любого места строки с помощью Flash Fill:

1. В ячейку рядом с первой строкой вручную введите числа, содержащиеся в строке.
   
2. Если все ваши данные находятся в непрерывном столбце, выберите ячейку непосредственно под ним.
   
3. Если в ваших данных есть пробелы, выберите первую ячейку и все ячейки ниже, из которых вы хотите извлечь числа, включая все пустые строки.
   
4. Нажимать Ctrl+Е в Windows или Ctrl+E на Mac, чтобы запустить Flash Fill.
5. Теперь ячейки должны заполниться извлеченными числами.
   

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

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

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

1. Выберите ячейку, в которой вы хотите, чтобы извлеченный номер появился.
2. Введите следующую формулу, заменив «B9» на ячейку, содержащую вашу строку:

   =СУММПРОИЗВ(СРЕДН(0&B9,НАИБОЛЬШИЙ(ИНДЕКС(ЧИСЛО(--СРЕДН(B9,СТРОКА(ДВССЫЛ("1:"&ДЛСТР(B9))),1))*СТРОКА(ДВССЫЛ("1:"&ДЛСТР(B9) )),0),СТРОКА(ДВССЫЛ("1:"&ДЛСТР(B9))))+1,1)*10^СТРОКА(ДВССЫЛ("1:"&ДЛСТР(B9)))/10)

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

   =ЕСЛИОШИБКА(СУММПРОИЗВ(СРЕДН(0&B9,НАИБОЛЬШИЙ(ИНДЕКС(ЕСЧИСЛО(--СРЕДН(B9,СТРОКА(ДВССЫЛ("1:"&ДЛСТР(B9))),1))*СТРОКА(ДВССЫЛ("1:"&ДЛСТР( B9))),0),СТРОКА(ДВССЫЛ("1:"&ДЛСТР(B9))))+1,1)*10^СТРОКА(ДВССЫЛ("1:"&ДЛСТР(B9)))/10)," ")

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

Расширьте свои навыки работы с Excel

Узнав, как извлечь число из строки в Excel, вы сможете быстро извлечь нужные данные из списка строк. Хотя требуемые формулы могут быть сложными, вам не нужно точно понимать, как они работают; только так и делают!

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