Как извлечь текст до/после второго пробела или запятой в Excel?
При работе с текстовыми данными в Excel вы можете часто сталкиваться со строками, содержащими несколько пробелов, запятых или других разделителей. Хотя функция «Текст по столбцам» в Excel может разделить содержимое ячейки на отдельные столбцы на основе выбранного разделителя, бывают случаи, когда требуется более конкретное решение — например, извлечение всего, что находится до или после второго пробела или запятой в ячейке. В этой статье описаны несколько практических методов для достижения этого, включая формулы, которые нацелены на текст до или после второго пробела/запятой, надежные решения с использованием макросов VBA для автоматизации или больших наборов данных, и универсальные варианты с использованием Power Query для продвинутой предварительной обработки данных.
Извлечение текста до второго пробела или запятой с помощью формулы
Извлечение текста после второго пробела или запятой с помощью формулы
Извлечение текста до или после второго разделителя (пробела/запятой) с помощью кода VBA
Извлечение текста до второго пробела или запятой с помощью формулы
Если вы хотите выделить часть ячейки, которая появляется до второго пробела, вы можете использовать комбинацию функций Excel.
1. Введите следующую формулу в пустую ячейку, где вы хотите отобразить результат (например, ячейку C2):
=IF(ISERROR(FIND(" ",A2,FIND(" ",A2,1)+1)),A2,LEFT(A2,FIND(" ",A2,FIND(" ",A2,1)+1)))2. Нажмите Enter. Затем используйте маркер заполнения, чтобы протянуть формулу вниз по всем строкам, которые вы хотите обработать. Результат покажет весь текст до второго пробела в каждой соответствующей строке — см. скриншот ниже:
Совет: Если вы хотите извлечь текст до второй запятой или другого разделителя, просто замените символ пробела (" ") в формуле на нужный вам разделитель. Например, для запятой:
=IF(ISERROR(FIND(",",A2,FIND(",",A2,1)+1)),A2,LEFT(A2,FIND(",",A2,FIND(",",A2,1)+1)))Этот подход быстр и эффективен для однократных извлечений или при работе с умеренно большими наборами данных. Однако, если текст сильно нерегулярен или количество разделителей значительно различается между строками, могут потребоваться дополнительные корректировки формулы. Также будьте внимательны к лишним пробелам или пустым ячейкам, которые могут повлиять на результаты. Всегда дважды проверяйте вывод, применяя его к важным данным.
Раскройте магию Excel с Kutools AI
- Умное выполнение: Выполняйте операции с ячейками, анализируйте данные и создавайте диаграммы — всё это посредством простых команд.
- Пользовательские формулы: Создавайте индивидуальные формулы для оптимизации ваших рабочих процессов.
- Кодирование VBA: Пишите и внедряйте код VBA без особых усилий.
- Интерпретация формул: Легко разбирайтесь в сложных формулах.
- Перевод текста: Преодолейте языковые барьеры в ваших таблицах.
Извлечение текста после второго пробела или запятой с помощью формулы
Чтобы извлечь текст, который появляется после второго разделителя-пробела в ячейке, вы можете использовать функции СЖПРОБ и ПОИСК вместе.
1. Введите следующую формулу в целевую ячейку (например, D2):
=MID(A2, FIND(" ", A2, FIND(" ", A2)+1)+1,256)2. Нажмите Enter. Протяните маркер заполнения вниз через все строки, требующие извлечения. Это покажет все после второго пробела для каждой строки, как показано на скриншоте:
Совет: Чтобы извлечь текст после второй запятой или любого другого разделителя, замените символ пробела в формуле на ваш требуемый разделитель. Для запятой формула будет:
=MID(A2, FIND(",", A2, FIND(",", A2)+1)+1,256)Извлечение текста до или после второго разделителя (пробела/запятой) с помощью кода VBA
Для больших наборов данных, повторяющихся извлечений или сценариев автоматизации использование макроса VBA является эффективным решением. VBA позволяет вам извлекать текст до или после второго пробела, запятой или любого другого разделителя — повышая эффективность при работе с разными структурами данных или необходимости повторяемости рутинных операций.
1. Выберите Разработчик > Visual Basic. В открывшемся окне Microsoft Visual Basic for Applications нажмите Вставить > Модуль и введите следующий код в окно модуля:
Sub ExtractTextSecondDelimiter()
Dim rng As Range
Dim cell As Range
Dim sep As String
Dim direction As String
Dim arr As Variant
Dim result As String
Dim pos1 As Long
Dim pos2 As Long
Dim xTitleId As String
Dim outputCell As Range
Dim i As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.Selection
Set rng = Application.InputBox("Select text range to extract from", xTitleId, rng.Address, Type:=8)
If rng Is Nothing Then Exit Sub
sep = Application.InputBox("Enter delimiter (e.g. space or comma)", xTitleId, " ", Type:=2)
If sep = "" Then Exit Sub
direction = Application.InputBox("Type 'before' for text before, 'after' for text after the 2nd delimiter", xTitleId, "before", Type:=2)
If direction = "" Then Exit Sub
Set outputCell = Application.InputBox("Select the first cell to output the result", xTitleId, Type:=8)
If outputCell Is Nothing Then Exit Sub
Application.ScreenUpdating = False
i = 0
For Each cell In rng
If Not IsEmpty(cell.Value) Then
pos1 = InStr(1, cell.Value, sep)
If pos1 > 0 Then
pos2 = InStr(pos1 + 1, cell.Value, sep)
If pos2 > 0 Then
If LCase(direction) = "before" Then
result = Left(cell.Value, pos2 - 1)
ElseIf LCase(direction) = "after" Then
result = Mid(cell.Value, pos2 + Len(sep))
Else
result = cell.Value
End If
Else
result = cell.Value
End If
Else
result = cell.Value
End If
outputCell.Offset(i, 0).Value = result
End If
i = i + 1
Next
Application.ScreenUpdating = True
MsgBox "Extraction completed.", vbInformation, xTitleId
End Sub
2После вставки кода нажмите 
Выполнить кнопку или нажмите F5 для выполнения. Диалоговое окно запросит вас выбрать диапазон данных, затем ввести разделитель, указать, хотите ли вы текст «до» или «после» второго вхождения, и, наконец, выбрать ячейку для вывода результата. Макрос выведет извлеченный результат в указанную вами ячейку.
Лучшие инструменты для повышения продуктивности в 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-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек