Как извлечь числа только из текстовой строки в Excel?
При работе в Excel часто встречаются ячейки, где числа и текст смешаны — например, «Заказ 1234» или «Оценка составляет 95,6». Извлечение только числовых значений из таких строк бывает необходимо для дальнейшего анализа, расчётов или подготовки отчётов. В зависимости от того, расположено ли число в начале, в конце или внутри текста, а также требуется ли извлекать исключительно десятичные значения, Excel предлагает несколько практичных способов решения этой задачи.
В этом руководстве мы рассмотрим различные методы извлечения чисел из текстовых строк в Excel, охватывающие разные сценарии: извлечение чисел из любого места строки, работу с десятичными числами, а также получение чисел строго с начала или конца строки. Каждый метод будет описан с точки зрения его принципа работы, удобства использования и практических рекомендаций для эффективного применения.
Извлечение числа из любого места текстовой строки
Извлечение только десятичного числа из текстовой строки
Извлечение числа из конца текстовой строки
Извлечение числа из начала текстовой строки
Извлечение числа из любого места текстовой строки
Извлечение чисел, встроенных в любое место текстовой строки, — распространённая задача, особенно при очистке данных из различных источников или объединении информации с нескольких листов. Для этого сценария существует несколько практичных и эффективных решений, каждое из которых идеально подходит для определённых случаев в зависимости от вашей версии Excel и уровня владения формулами или надстройками.
♦ Извлечение числа из любого места текстовой строки с помощью «Быстрого заполнения»
«Быстрое заполнение» — это удобная и быстрая функция, доступная в Excel 2013 и более поздних версиях, которая извлекает числа из текстовой строки на основе шаблона, продемонстрированного вами. Решение не требует формул или макросов, что делает его практичным выбором для простых сценариев:
1. В ячейке рядом с вашей текстовой строкой вручную введите число именно так, как вы хотите его извлечь (например, если ячейка A2 содержит «Заказ123A», введите «123» в B2).
2. Активируйте ячейку под вашей записью и нажмите Ctrl + E(или воспользуйтесь вкладкой)Данные > Быстрое заполнение). Excel автоматически заполнит остальные ячейки, извлекая числа из аналогичных строк на основе вашего первого примера.
Этот метод идеально подходит для разовых задач с небольшими и средними наборами данных, имеющими согласованную структуру. Имейте в виду: «Быстрое заполнение» работает на основе распознавания шаблонов — если ваши данные сильно несогласованы или содержат хаотичное сочетание цифр и текста, извлечение может быть выполнено некорректно. Если функция не распознаёт шаблон автоматически, убедитесь, что она включена через меню Файл>Параметры>Дополнительно>Автоматическое быстрое заполнение.
♦ Извлечение числа из любого места текстовой строки с помощью формул
Использование формул — универсальный способ извлечения чисел из текстовых строк. Этот метод задействует встроенные или динамические массивные функции и особенно полезен, если вам нужно решение, автоматически обновляющееся при изменении исходных данных.
Выберите пустую ячейку, в которой вы хотите отобразить извлечённое число. Затем введите одну из приведённых ниже формул — в зависимости от вашей версии Excel. После ввода формулы перетащите маркер заполнения вниз, чтобы при необходимости применить её к другим ячейкам. См. иллюстрацию ниже для справки:
● Все версии Excel:
=IF(SUM(LEN(A2)-LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"}, "")))>,0, SUMPRODUCT(MID(0&,A2, LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("$1:$"&,LEN(A2))),1))* ROW(INDIRECT("$1:$"&,LEN(A2))),0), ROW(INDIRECT("$1:$"&,LEN(A2))))+1,1) * 10^ROW(INDIRECT("$1:$"&,LEN(A2)))/10),"")Эта формула совместима со всеми современными версиями Excel и станет надёжным решением, если вы не используете Excel 365 или Excel 2021. Учтите, что формулы с большим количеством вложенных функций могут замедлить работу с объёмными наборами данных. Кроме того, убедитесь, что текстовые строки содержат хотя бы один числовой символ — в противном случае формула может вернуть пустое значение или ошибку.
● Excel 365 или Excel 2021 и более поздние версии:
=TEXTJOIN("", TRUE, IFERROR(MID(A2, SEQUENCE(LEN(A2)), 1) *1, ""))Эта формула использует новые функции динамических массивов, что значительно упрощает извлечение данных для пользователей Office 365 или Excel 2021 и новее. Она эффективно справляется как с простыми, так и со сложными буквенно-цифровыми комбинациями. Убедитесь, что вы используете подходящую версию — в противном случае формула может работать некорректно.
Совет: при работе с формулами, содержащими отрицательные или смешанные числа, а также числа со специальными символами, обязательно проверяйте точность полученных результатов. Кроме того, формулы по умолчанию могут не распознавать десятичные точки — если вам нужно извлекать десятичные числа, ознакомьтесь с соответствующим разделом ниже. В случае неожиданного результата или ошибки дважды проверьте ссылки на ячейки и синтаксис формулы.
♦ Извлечение числа из любого места текстовой строки с помощью Kutools для Excel
Kutools для Excel предоставляет простой метод извлечения чисел из любого места текстовой строки. Особенно подходит пользователям, предпочитающим интерактивный интерфейс вместо формул, или тем, кто должен обработать большой диапазон без ручных усилий. Этот метод Экономия времени и упрощает задачи, избавляя от необходимости запоминать или отлаживать формулы.
- Нажмите Kutools > Текст > Извлечь текст, как показано ниже.
- В диалоговом окне Извлечь текст:
(1.) Выделите диапазон ячеек, содержащих текстовые строки;
(2.) Выберите параметр Извлечь числа, чтобы извлечь только числовые фрагменты;
(3.) Если вы хотите, чтобы извлечённые числа автоматически обновлялись при изменении исходных данных, установите флажок Вставить как формулу (в противном случае результаты останутся статическим текстом);
(4.) Нажмите ОК.
- Когда появится запрос, выберите ячейку для вывода результатов. Убедитесь, что вы указываете пустую область с достаточным количеством места, чтобы избежать перезаписи существующих данных.
- Нажмите ОК — числа из каждой выбранной ячейки будут немедленно извлечены.
Этот подход прост в использовании и минимизирует риск ошибок в формулах или некорректного применения ссылок на ячейки. Однако имейте в виду, что Kutools — это надстройка, отсутствующая в стандартной версии Excel. Она идеально подходит для пакетной обработки и для тех, кто хочет избежать сложного синтаксиса функций или VBA.
Если числа извлекаются не так, как вы ожидали, проверьте исходный текст на наличие смешанного форматирования — например, скрытых символов или нестандартных цифр. Кроме того, всегда создавайте резервную копию листа перед выполнением массовых изменений.
♦ Извлечение числа из любого места текстовой строки с помощью кода VBA
Для продвинутых пользователей или нестандартных ситуаций, когда формулы и надстройки не справляются, VBA предоставляет гибкий способ извлекать числа из текстовых строк в Excel. Этот подход легко настраивается и особенно эффективен для автоматизации повторяющихся задач или обработки нетиповых наборов данных.
- Откройте редактор VBA, нажав Alt + F11. В окне Microsoft Visual Basic для приложений выберите Вставка > Модуль. В появившемся окне модуля вставьте следующий код:
- Код 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 - После вставки кода нажмите F5 или кнопку Выполнить. Появится окно с запросом на выбор диапазона текстовых строк для обработки.
- После выбора диапазона нажмите ОК. Затем появится второе окно с запросом указать начальную ячейку для вывода.
- Снова нажмите ОК. Все числа, содержащиеся в исходном диапазоне, будут извлечены и помещены в указанную вами область вывода.
Преимущества метода VBA — эффективная обработка больших объёмов данных и гибкая настройка поведения (например, извлечение только первого числа, работа с десятичными значениями или исключение определённых символов) при наличии базовых навыков работы с VBA. Меры предосторожности: макросы VBA нельзя отменить, и они могут перезаписать данные, если выбранная Область размещения списка пересекается с исходными данными, поэтому всегда тестируйте их на резервной копии файла. Убедитесь, что макросы включены в Excel.
Извлечение только десятичного числа из текстовой строки
Иногда ваши текстовые строки могут содержать числа с десятичными точками (например, «вес: 15,25 кг»). Если вам нужно извлечь именно десятичные числа — включая саму десятичную точку — стандартные формулы могут работать некорректно. Приведённые ниже решения созданы специально для таких случаев:
Следующие формулы позволят эффективно извлекать десятичные числа из текстовых строк. Введите подходящую формулу в пустую ячейку рядом с вашими данными, а затем перетащите маркер заполнения, чтобы скопировать её в нужные строки.
● Все версии Excel:
=LOOKUP(9.9E+307,--LEFT(MID(A2,MIN(FIND({1,2,3,4,5,6,7,8,9,0}, $A2&,"1023456789")),999),ROW(INDIRECT("1:999"))))Эта формула совместима с большинством версий Excel и предназначена для корректного распознавания десятичной точки как части числа при извлечении. Внимательно проверьте свои данные на наличие лишних точек в тексте. Если десятичные числа извлекаются некорректно, проанализируйте исходную строку на нестандартные символы или ошибки форматирования.
● Excel 365:
=REGEXEXTRACT(A2,"[\d.]+")В Excel 365 динамические массивы позволяют более интеллектуально обрабатывать и извлекать десятичные числа из сложных текстовых строк. Введите формулу в целевую ячейку, нажмите Enter и, при необходимости, протяните её вниз.
Совет: если вместо чисел вы постоянно получаете нули или ошибки, убедитесь, что ваши данные содержат корректные десятичные числа и что ссылки на ячейки указаны правильно. Если ваши числа могут быть как отрицательными, так и десятичными, возможно, потребуется скорректировать формулы.
Извлечение числа из конца текстовой строки
Когда числа всегда стоят в конце текстовых строк (например, «221_ProductID_446» или «923Ticket#2021»), часто возникает необходимость извлечь только завершающее число («446» или «2021»). Представленная здесь формула специально разработана для такого сценария и надёжно извлекает только цифровые последовательности, расположенные в самом конце текста.
Скопируйте и вставьте следующую формулу в пустую ячейку. В старых версиях Excel подтвердите формулу сочетанием клавиш Ctrl + Shift + Enter, чтобы создать формулу массива. В новых версиях Excel с поддержкой динамических массивов достаточно нажать Enter:
=RIGHT(A2, LEN(A2) - MAX(IF(ISNUMBER(MID(A2, ROW(INDIRECT("1:"&,LEN(A2))), 1) *1)=FALSE, ROW(INDIRECT("1:"&,LEN(A2))), 0)))Протяните маркер заполнения вниз, чтобы применить эту формулу ко всем строкам. Этот метод идеально подходит для единообразных форматов, где нужно извлекать только последнее число в каждой строке, игнорируя числа в начале или середине.
Примечание: если ваши данные содержат пробелы, специальные символы или различия в расположении цифр, скорректируйте формулу или воспользуйтесь более универсальным методом, описанным выше. Всегда сверяйте полученный результат с ожидаемым, особенно когда текст в ваших данных завершается нечисловыми символами, похожими на цифры.
Извлечение числа из начала текстовой строки
Если вам нужно извлечь только числа, стоящие строго в начале текстовых строк, воспользуйтесь специальной формулой — она точно выделит именно эту часть.
Поместите формулу в пустую целевую ячейку. В старых версиях Excel используйте сочетание клавиш Ctrl + Shift + Enter, чтобы создать формулу массива (в Excel 365/2021 достаточно просто нажать)Enter):
=LEFT(A2, MATCH(FALSE, ISNUMBER(MID(A2, ROW(INDIRECT("1:"&,LEN(A2)+1)), 1) *1), 0) -1)Затем протяните формулу вниз ко всем строкам, требующим извлечения. Этот подход возвращает только числа из начала строки — даже если в других частях строки присутствуют дополнительные числа, будет захвачена лишь начальная цифровая часть.
Совет: если первый символ в строке не является цифрой, формула может вернуть пустое значение или ошибку. При необходимости проверьте и очистите исходные данные, чтобы добиться наилучших результатов.
Извлечение чисел из текстовых строк — распространённая и зачастую критически важная задача при подготовке данных в Excel. Выбор подходящего метода зависит от структуры ваших данных, версии Excel и того, насколько вам комфортно работать с формулами или надстройками. Если ваш случай не охватывается описанными выше решениями, попробуйте комбинировать разные методы или воспользуйтесь специализированными надстройками для решения сложных задач. Чтобы избежать типичных ошибок, всегда проверяйте извлечённые результаты на несоответствия, делайте резервные копии и тестируйте подходы на небольших выборках данных.
См. также:
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек