Перейти к содержимому

Kutools для Office — один пакет. Пять инструментов. Выполняйте больше.

Excel: удаление символов, слов, чисел из текстовых строк

Author Xiaoyang Last modified
 

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

Содержание:

1. Удалить символы слева, справа или из середины текстовых строк

2. Удалить ненужные / специальные символы из текстовой строки

3. Удалить символы / текст до или после определенного символа

4. Удалить слова из текстовых строк

Удалить символы слева, справа или из середины текстовых строк

Для большинства пользователей задача удаления символов слева, справа или из середины текстовых строк в Excel является довольно распространенной. В этом разделе представлены быстрые и простые способы решения этой задачи.

1.1 Удалить первые n символов из текстовых строк

Если вам нужно удалить первые n символов из списка текстовых строк, воспользуйтесь одним из следующих методов.

С помощью формул

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

Функция REPLACE для удаления первых N символов:

=REPLACE(string,1, num_chars, "")
  • "string": Текстовая строка, из которой вы хотите удалить символы;
  • "num_chars": Количество символов, которые нужно удалить.

Например, чтобы удалить первые2 символа из ячеек, используйте следующую формулу и протяните маркер заполнения для копирования формулы в другие ячейки, см. скриншот:

=REPLACE(A4, 1, 2, "")

Функции RIGHT и LEN для удаления первых N символов:

=RIGHT(string, LEN(string) - num_chars)
  • "string": Текстовая строка, из которой вы хотите удалить символы;
  • "num_chars": Количество символов, которые нужно удалить.

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

=RIGHT(A4,LEN(A4)-2)

С помощью пользовательской функции

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

1. Удерживайте клавиши "Alt + F11", чтобы открыть окно "Microsoft Visual Basic for Applications".

2. Нажмите "Вставка" > "Модуль" и вставьте следующий код в окно модуля.

VBA-код: Удалить первые n символов из текстовых строк

Function removeFirstx(rng As String, cnt As Long)
'Updateby Extendoffice
removeFirstx = Right(rng, Len(rng) - cnt)
End Function

3. Затем вернитесь на лист и введите формулу: "=removefirstx(A4,2)" в пустую ячейку, после чего протяните маркер заполнения вниз для получения результата, см. скриншот:

Примечание: В этой формуле: "A4" — ячейка, из которой вы хотите удалить символы; число "2" указывает количество символов, которые нужно удалить с начала строки.

A screenshot showing the result of using a custom VBA function to remove characters from text in Excel

1.2 Удалить последние n символов из текстовых строк

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

С помощью формулы

Для удаления последних n символов из текстовых строк можно использовать формулу на основе функций LEFT и LEN.

Функции LEFT и LEN для удаления последних N символов:

=LEFT(string, LEN(string) - num_chars)
  • "string": Текстовая строка, из которой вы хотите удалить символы;
  • "num_chars": Количество символов, которые нужно удалить.

Чтобы удалить3 символа с конца текстовых строк, используйте эту формулу и протяните маркер заполнения для копирования формулы в другие ячейки, см. скриншот:

=LEFT(A4, LEN(A4) - 3)

С помощью пользовательской функции

Пользовательская функция также поможет удалить последние n символов из списка ячеек. Следуйте этим шагам:

1. Удерживайте клавиши "Alt + F11", чтобы открыть окно "Microsoft Visual Basic for Applications".

2. Нажмите "Вставка" > "Модуль" и вставьте следующий код в окно модуля.

VBA-код: Удалить последние n символов из текстовых строк

Function removeLastx(rng As String, cnt As Long)
'Updateby Extendoffice
removeLastx = Left(rng, Len(rng) - cnt)
End Function

3. Затем вернитесь на лист и введите формулу: "=removelastx(A4,3)" в пустую ячейку, после чего протяните маркер заполнения вниз для получения результата, см. скриншот:

Примечание: В этой формуле: "A4" — ячейка, из которой вы хотите удалить символы; число "3" указывает количество символов, которые нужно удалить с конца строки.

A screenshot showing the result of using a custom VBA function to remove characters from the end of text in Excel

1.3 Удалить первые, последние n символов или символы на определенной позиции с помощью мощной функции

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

Kutools для Excel предлагает более 300 продвинутых функций для упрощения сложных задач, повышая креативность и эффективность. Интеграция с возможностями ИИ позволяет Kutools автоматизировать задачи с высокой точностью, делая управление данными без усилий. Подробная информация о Kutools для Excel...  Бесплатная пробная версия...

1. Выделите ячейки, из которых нужно удалить символы, затем нажмите "Kutools" > "Текст" > "Удалить символы в определенной позиции", см. скриншот:

2. В диалоговом окне "Удалить символы в определенной позиции" выполните следующие действия:

2.1 Удалить первые n символов из ячеек:

  • В поле "Число" введите количество символов, которые хотите удалить из строк. В этом примере я удалю первые2 символа.
  • Выберите опцию "Начать слева" в разделе "Положение".
  • Затем нажмите кнопку "Ок" или "Применить", чтобы получить результат, как показано на скриншоте ниже.

2.2 Удалить последние n символов из ячеек:

  • В поле "Число" введите количество символов, которые хотите удалить из строк. В этом примере я удалю последние3 символа.
  • Выберите опцию "Начать справа" в разделе "Положение".
  • Затем нажмите кнопку "Ок" или "Применить", чтобы получить результат, как показано на скриншоте ниже.

2.3 Удалить n символов с определенной позиции в ячейках:

Если нужно удалить определенное количество символов с конкретной позиции в строке, например, удалить3 символа, начиная с третьего символа строки.

  • В поле "Число" введите количество символов, которые хотите удалить из строк. В этом примере я удалю3 символа с определенной позиции.
  • Выберите опцию "Указать" и введите номер, с которого нужно начать удаление символов, в поле раздела "Положение". Здесь я удалю символы, начиная с третьего символа.
  • Затем нажмите кнопку "Ок" или "Применить", чтобы получить результат, как показано на скриншоте ниже.

Kutools для Excel - Усильте Excel более чем 300 необходимыми инструментами. Наслаждайтесь постоянно бесплатными функциями ИИ! Получите прямо сейчас

1.4 Удалить одновременно первые n и последние n символов из текстовых строк с помощью формулы

Если нужно удалить символы с обеих сторон текстовой строки в Excel, можно объединить функции MID и LEN для создания формулы, решающей эту задачу.

=MID(string, left_chars +1, LEN(string) - (left_chars + right_chars)
  • "string": Текстовая строка, из которой вы хотите удалить символы;
  • "left_chars": Количество символов для удаления слева;
  • "right_chars": Количество символов для удаления справа.

Например, если нужно удалить одновременно первые7 символов и последние5 символов из текстовой строки, введите следующую формулу в пустую ячейку:

=MID(A4, 7+1, LEN(A4) - (7+5))

Примечание: В этой формуле: "A4" — ячейка, из которой вы хотите удалить символы; число "7" — количество символов, которые нужно удалить слева; число "5" — количество символов, которые нужно удалить справа.

Затем протяните маркер заполнения вниз до нужной области, чтобы применить формулу, и вы получите результат, как показано на скриншоте ниже:

Удалить ненужные / специальные символы из текстовых строк

При импорте данных из других источников в Excel часто появляются специальные или ненужные символы в рабочем листе. Чтобы удалить такие символы, как #@$%^&, пробелы, числа, нечисловые символы, разрывы строк и т.д., в этом разделе приведены полезные методы для решения задачи.

2.1 Удалить некоторые специальные символы из текстовых строк

Если в текстовых строках встречаются специальные символы, такие как %^&*(), для их удаления можно воспользоваться одним из трех способов ниже.

Удалить несколько специальных символов из текстовых строк с помощью функции SUBSTITUTE

В Excel можно вложить несколько функций SUBSTITUTE, чтобы заменить каждый конкретный символ на пустое значение. Общий синтаксис:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(string_cell, char1, ""), char2, ""), char3, "")
  • "string_cell": Ячейка, содержащая текстовую строку, из которой нужно удалить специальные символы;
  • "char1, char2, char3": Ненужные символы, которые вы хотите удалить.

Теперь скопируйте или введите следующую формулу в пустую ячейку:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "#", ""), "&", ""), "*", ""), "%", "")

Затем протяните маркер заполнения вниз до нужных ячеек, и все указанные вами ненужные символы будут удалены сразу, см. скриншот:

Совет: Если нужно удалить больше символов, просто вложите больше функций SUBSTITUTE в формулу.

Удалить несколько специальных символов из текстовых строк с помощью пользовательской функции

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

1. Удерживайте клавиши "Alt + F11", чтобы открыть окно "Microsoft Visual Basic for Applications".

2. Нажмите "Вставка" > "Модуль" и вставьте следующий код в окно модуля.

VBA-код: Удалить несколько специальных символов из текстовых строк

Function RemoveUnwantedChars(Str As String, xchars As String)
'Updateby Extendoffice
    For Index = 1 To Len(xchars)
        Str = Replace(Str, Mid(xchars, Index, 1), "")
    Next
    RemoveUnwantedChars = Str
End Function

3. Затем закройте окно кода и вернитесь на лист, введите формулу "=RemoveUnwantedChars(A2, $D$2)" в пустую ячейку для вывода результата и протяните маркер заполнения вниз для получения результата, см. скриншот:

Примечание: В приведенной выше формуле: "A2" — ячейка, из которой нужно удалить символы; "$D$2" содержит специальные символы, которые вы хотите удалить (можно ввести любые другие нужные символы).

A screenshot showing the result of using a VBA User Defined Function to remove multiple special characters from text in Excel

Удалить несколько специальных символов из текстовых строк с помощью удобной функции

Если у вас установлен Kutools для Excel, с помощью функции Удалить определенные символы можно удалить любые символы — числовые, буквенные, непечатаемые и т.д. — из списка ячеек по вашему выбору.

Kutools для Excel предлагает более 300 продвинутых функций для упрощения сложных задач, повышая креативность и эффективность. Интеграция с возможностями ИИ позволяет Kutools автоматизировать задачи с высокой точностью, делая управление данными без усилий. Подробная информация о Kutools для Excel...  Бесплатная пробная версия...

1. Выделите диапазон ячеек, из которых нужно удалить специальные символы, затем нажмите "Kutools" > "Текст" > "Удалить определенные символы", см. скриншот:

2. В диалоговом окне "Удалить определенные символы":

  • Отметьте опцию "Пользовательский" в разделе "Удалить определенные символы".
  • Затем введите специальные символы в поле, которые хотите удалить.
  • Далее нажмите кнопку "Ок" или "Применить", чтобы сразу удалить указанные символы. См. скриншот:

Kutools для Excel - Усильте Excel более чем 300 необходимыми инструментами. Наслаждайтесь постоянно бесплатными функциями ИИ! Получите прямо сейчас

2.2 Удалить все числа из текстовых строк

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

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

В Excel вложенная функция SUBSTITUTE поможет заменить все числа на пустое значение, поэтому используйте следующую формулу для удаления всех чисел из ячеек:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,""),0,"")

Затем протяните маркер заполнения вниз до нужных ячеек, и все числа будут удалены из списка текстовых строк, см. скриншот:

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

Если у вас Excel2019,2021 или365, новая функция TEXTJOIN также поможет удалить числа из текстовых строк.

Скопируйте следующую формулу в пустую ячейку и одновременно нажмите Ctrl + Shift + Enter для получения первого результата:

=TEXTJOIN("", TRUE, IF(ISERR(MID(A2, ROW(INDIRECT( "1:"&LEN(A2) )), 1) *1), MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1), ""))

Затем скопируйте формулу в другие ячейки ниже, где хотите применить формулу, см. скриншот:

Примечание: TEXTJOIN доступна только в Excel2019,2021 и Office365.

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

Кроме двух вышеуказанных формул, пользовательская функция также поможет вам, выполните следующие шаги:

1. Удерживайте клавиши "Alt + F11", чтобы открыть окно "Microsoft Visual Basic for Applications".

2. Нажмите "Вставка" > "Модуль" и вставьте следующий код в окно модуля.

VBA-код: Удалить числа из текстовых строк

Function RemoveNumbers(Txt As String) As String
'Updateby Extendoffice
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "[0-9]"
RemoveNumbers = .Replace(Txt, "")
End With
End Function

3. Затем закройте и выйдите из окна кода, вернитесь на лист и введите формулу: =RemoveNumbers(A2) в пустую ячейку, затем протяните маркер заполнения вниз до нужных ячеек, см. скриншот:

A screenshot showing the VBA User Defined Function result to remove numbers from text strings in Excel

Удалить числа из текстовых строк с помощью удобной опции

Если вам надоели сложные формулы, воспользуйтесь простым инструментом — функцией Удалить определенные символы в Kutools для Excel. С помощью этой функции задача решается в несколько кликов.

Kutools для Excel предлагает более 300 продвинутых функций для упрощения сложных задач, повышая креативность и эффективность. Интеграция с возможностями ИИ позволяет Kutools автоматизировать задачи с высокой точностью, делая управление данными без усилий. Подробная информация о Kutools для Excel...  Бесплатная пробная версия...

1. Выделите диапазон ячеек, из которых нужно удалить числа, затем нажмите "Kutools" > "Текст" > "Удалить определенные символы".

2. В диалоговом окне "Удалить определенные символы" выполните следующие действия:

  • Отметьте опцию "Числовые" в разделе "Удалить определенные символы".
  • Затем нажмите кнопку "Ок" или "Применить", чтобы сразу удалить числа. См. скриншот:

Kutools для Excel - Усильте Excel более чем 300 необходимыми инструментами. Наслаждайтесь постоянно бесплатными функциями ИИ! Получите прямо сейчас

2.3 Удалить нечисловые символы из текстовых строк

Чтобы удалить все нечисловые символы и оставить только числа из текстовых строк, в этом разделе рассмотрены способы решения задачи в Excel.

Удалить нечисловые символы из текстовых строк с помощью формулы в Excel2016 и более ранних версиях

Если вы используете Excel2016 или более ранние версии, примените сложную формулу для выполнения задачи, скопируйте или введите следующую формулу в пустую ячейку:

=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)

Затем скопируйте формулу в другие ячейки ниже, где хотите применить формулу, см. скриншот:

Примечание: Если числа в текстовой строке начинаются с0, этот0 будет потерян.

Удалить нечисловые символы из текстовых строк с помощью функции TEXTJOIN в Excel2019,2021,365

Приведенная выше формула может быть слишком сложной для большинства пользователей. Если у вас Excel2019,2021 или365, есть простая формула, которая поможет вам.

Скопируйте или введите следующую формулу в пустую ячейку и одновременно нажмите клавиши "Ctrl + Shift + Enter" для получения первого корректного результата:

=TEXTJOIN("",TRUE,IFERROR(MID(A2,ROW(INDIRECT("1:100")),1)+0,""))

Затем протяните маркер заполнения вниз до нужных ячеек, и вы получите результат, как показано на скриншоте ниже:

Примечание: С этой формулой ведущие нули сохраняются, так как числа возвращаются в виде текста.

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

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

1. Удерживайте клавиши "Alt + F11", чтобы открыть окно "Microsoft Visual Basic for Applications".

2. Нажмите "Вставка" > "Модуль" и вставьте следующий код в окно модуля.

VBA-код: Удалить нечисловые символы из текстовых строк

Function Removenonnumeric(str As String) As String
'Updateby Extendoffice
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "[^0-9]"
        Removenonnumeric = .Replace(str, "")
    End With
End Function

3. Затем закройте и выйдите из окна кода, вернитесь на лист и введите формулу: =Removenonnumeric(A2) в пустую ячейку, затем протяните маркер заполнения вниз до нужных ячеек — будут извлечены только числа, как показано на скриншоте ниже:

A screenshot showing the result of using a VBA User Defined Function to extract only numbers from text strings in Excel

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

Для удаления нечисловых символов в диапазоне ячеек напрямую используйте функцию Удалить определенные символы в Kutools для Excel — задача решается в несколько кликов.

Kutools для Excel предлагает более 300 продвинутых функций для упрощения сложных задач, повышая креативность и эффективность. Интеграция с возможностями ИИ позволяет Kutools автоматизировать задачи с высокой точностью, делая управление данными без усилий. Подробная информация о Kutools для Excel...  Бесплатная пробная версия...

1. Выделите диапазон ячеек, из которых нужно удалить нечисловые символы, затем нажмите "Kutools" > "Текст" > "Удалить определенные символы".

2. В диалоговом окне "Удалить определенные символы" выполните следующие действия:

  • Отметьте опцию "Нечисловые" в разделе "Удалить определенные символы".
  • Затем нажмите кнопку "Ок" или "Применить", чтобы сразу удалить все нечисловые символы. См. скриншот:

Kutools для Excel - Усильте Excel более чем 300 необходимыми инструментами. Наслаждайтесь постоянно бесплатными функциями ИИ! Получите прямо сейчас

2.4 Разделить текст и числа из одной ячейки на два столбца

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

Разделить текст и числа из одной ячейки на два столбца с помощью пользовательской функции

С помощью следующей пользовательской функции вы можете сразу извлечь текст и числа, выполните следующие шаги:

1. Удерживайте клавиши "Alt + F11", чтобы открыть окно "Microsoft Visual Basic for Applications".

2. Нажмите "Вставка" > "Модуль" и вставьте следующий код в окно модуля.

VBA-код: Разделить текст и числа из текстовых строк на два столбца

Function SplitText(pWorkRng As Range, pIsNumber As Boolean) As String
'Updateby Extendoffice
Dim xLen As Long
Dim xStr As String
xLen = VBA.Len(pWorkRng.Value)
For i = 1 To xLen
    xStr = VBA.Mid(pWorkRng.Value, i, 1)
    If ((VBA.IsNumeric(xStr) And pIsNumber) Or (Not (VBA.IsNumeric(xStr)) And Not (pIsNumber))) Then
        SplitText = SplitText + xStr
    End If
Next
End Function

3. Затем закройте и выйдите из окна кода, вернитесь на лист и введите формулу: =SplitText(A2,FALSE) в пустую ячейку, затем протяните маркер заполнения вниз до нужных ячеек, чтобы получить весь текст, см. скриншот:

A screenshot showing the result of using a User Defined Function to separate text from numbers in Excel

4. Затем введите формулу: =SplitText(A2,TRUE) в другую ячейку и протяните маркер заполнения вниз до нужных ячеек, чтобы получить числа, см. скриншот:

A screenshot showing the result of using a User Defined Function to extract numbers from a text string in Excel

Разделить текст и числа из одной ячейки на два столбца с помощью простой функции

Если у вас есть Kutools для Excel, его функция Разделить ячейки поможет разделить ячейки на несколько столбцов или строк по любым разделителям, заданной ширине или по тексту и числу.

Kutools для Excel предлагает более 300 продвинутых функций для упрощения сложных задач, повышая креативность и эффективность. Интеграция с возможностями ИИ позволяет Kutools автоматизировать задачи с высокой точностью, делая управление данными без усилий. Подробная информация о Kutools для Excel...  Бесплатная пробная версия...

1. Выделите диапазон ячеек, которые хотите разделить, затем нажмите "Kutools" > "Объединить и разделить" > "Разделить ячейки", см. скриншот:

2. В диалоговом окне "Разделить ячейки" выберите опцию "Разделить данные" в разделе "Тип", затем отметьте "Разделить по тексту и числу" в разделе "Разделитель", см. скриншот:

3. Затем нажмите кнопку "Ок", появится еще одно диалоговое окно "Разделить ячейки", выберите ячейку для вывода разделенного текста и чисел, затем нажмите кнопку "ОК". Теперь вы увидите, что текст и числа в выбранных ячейках разделены на два столбца сразу, как показано в демонстрации ниже:

Kutools для Excel - Усильте Excel более чем 300 необходимыми инструментами. Наслаждайтесь постоянно бесплатными функциями ИИ! Получите прямо сейчас

2.5 Удалить символы разрыва строки из текстовых строк

Разрыв строки позволяет размещать несколько строк в одной ячейке Excel. Иногда при копировании данных с сайта или при ручном разделении содержимого ячейки с помощью "Alt + Enter" появляются разрывы строк или возвраты каретки. В некоторых случаях может понадобиться удалить разрывы строк, чтобы содержимое ячейки отображалось в одну строку, как показано на скриншоте ниже. Здесь я расскажу о способах решения этой задачи в Excel.

Удалить символы разрыва строки из текстовых строк с помощью функции Поиск и Замена

В Excel вы можете использовать функцию "Поиск и Замена" для удаления разрывов строк, выполните следующие действия:

1. Выделите диапазон данных, из которого нужно удалить разрывы строк.

2. Затем нажмите "Главная" > "Найти и выделить" > "Заменить" (или нажмите клавиши "Ctrl + H"), чтобы открыть диалоговое окно "Поиск и Замена", см. скриншот:

3. В появившемся диалоговом окне "Поиск и Замена" выполните следующие действия:

  • Поместите курсор в поле "Найти" и нажмите "Ctrl + J" на клавиатуре — вы ничего не увидите, но символ разрыва строки будет вставлен.
  • В поле "Заменить на" оставьте это поле пустым, чтобы просто удалить разрывы строк, или нажмите пробел один раз, чтобы заменить разрывы строк на пробелы.

4. Затем нажмите кнопку "Заменить все" — все разрывы строк в выбранных ячейках будут удалены или заменены на пробелы сразу. См. скриншот:

Удалить символы разрыва строки из текстовых строк с помощью функции SUBSTITUTE

Также можно создать формулу на основе функций SUBSTITUTE и CHAR для удаления разрывов строк из текстовых строк.

Примените следующую формулу для получения результата:

=SUBSTITUTE(A2,CHAR(10),"")

Совет: Функция SUBSTITUTE находит и заменяет символ CHAR(10), который представляет разрыв строки, на пустое значение. Если хотите, чтобы результат разделялся запятой и пробелом, используйте формулу ниже:

=SUBSTITUTE(A2,CHAR(10),", ")

Удалить символы разрыва строки из текстовых строк с помощью VBA-кода

Если вы уверенно пользуетесь VBA, здесь также приведен код для вас, выполните следующие шаги:

1. Удерживайте клавиши "Alt + F11", чтобы открыть окно "Microsoft Visual Basic for Applications".

2. Нажмите "Вставка" > "Модуль" и вставьте следующий код в окно модуля.

VBA-код: Удалить разрывы строк из текстовых строк

Sub RemoveCarriage()
'Updateby Extendoffice
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    Rng.Value = Replace(Rng.Value, Chr(10), "")
Next
End Sub

3. Затем нажмите клавишу "F5" для запуска кода, появится окно с запросом. Выберите диапазон, из которого нужно удалить разрывы строк, как показано на скриншоте ниже:

A screenshot of a prompt box asking the user to select a range to remove line breaks in Excel

4. Затем нажмите кнопку "ОК" — все разрывы строк будут удалены из выбранного диапазона данных.

Удалить символы разрыва строки из текстовых строк с помощью умной опции

В Kutools для Excel функция Удалить определенные символы также поможет легко удалить разрывы строк.

Kutools для Excel предлагает более 300 продвинутых функций для упрощения сложных задач, повышая креативность и эффективность. Интеграция с возможностями ИИ позволяет Kutools автоматизировать задачи с высокой точностью, делая управление данными без усилий. Подробная информация о Kutools для Excel...  Бесплатная пробная версия...

1. Выделите диапазон ячеек, из которых нужно удалить разрывы строк, затем нажмите "Kutools" > "Текст" > "Удалить определенные символы".

2. В диалоговом окне "Удалить определенные символы" выполните следующие действия:

  • Отметьте опцию "Непечатаемые символы" в разделе "Удалить определенные символы".
  • Затем нажмите кнопку "Ок" или "Применить", чтобы удалить все разрывы строк из выбранного диапазона данных. См. скриншот:

Kutools для Excel - Усильте Excel более чем 300 необходимыми инструментами. Наслаждайтесь постоянно бесплатными функциями ИИ! Получите прямо сейчас

2.6 Удалить пробелы (в начале, в конце, лишние или все пробелы) из текстовых строк

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

Удалить лишние пробелы (в начале, в конце, между словами) из текстовых строк с помощью функции TRIM

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

Введите следующую формулу в пустую ячейку:

=TRIM(A2)

Затем протяните маркер заполнения вниз для копирования формулы в другие ячейки — теперь все начальные, конечные и лишние пробелы между словами будут удалены сразу, как показано на скриншоте:

Удалить все пробелы из текстовых строк

Если нужно удалить все пробелы из текстовых строк, используйте функцию SUBSTITUTE или функцию Поиск и Замена.

С помощью функции SUBSTITUTE

Используйте функцию SUBSTITUTE для замены всех пробелов на пустое значение, примените следующую формулу в пустую ячейку:

=SUBSTITUTE(A2," ","")

Затем протяните маркер заполнения вниз для копирования формулы в другие ячейки — все пробелы будут удалены, как показано на скриншоте ниже:

С помощью функции Поиск и Замена

В Excel функция "Поиск и Замена" также поможет удалить все пробелы из выбранных ячеек, выполните следующие шаги:

1. Выделите диапазон данных, из которого нужно удалить все пробелы.

2. Затем нажмите "Главная" > "Найти и выделить" > "Заменить" (или нажмите клавиши "Ctrl + H"), чтобы открыть диалоговое окно "Поиск и Замена", и выполните следующие действия:

  • Нажмите пробел в поле "Найти";
  • В поле "Заменить на" оставьте это поле пустым.

3. Затем нажмите кнопку "Заменить все" — все пробелы в выбранных ячейках будут удалены сразу. См. скриншот:

Удалить все виды пробелов из текстовых строк с помощью мощной функции

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

Kutools для Excel предлагает более 300 продвинутых функций для упрощения сложных задач, повышая креативность и эффективность. Интеграция с возможностями ИИ позволяет Kutools автоматизировать задачи с высокой точностью, делая управление данными без усилий. Подробная информация о Kutools для Excel...  Бесплатная пробная версия...

1. Выделите диапазон данных, из которого нужно удалить пробелы, затем нажмите "Kutools" > "Текст" > "Удалить пробелы". См. скриншот:

2. В диалоговом окне "Удалить пробелы" выберите тип пробела, который хотите удалить, в разделе "Тип пробелов":

  • Чтобы удалить начальные пробелы, выберите опцию "Пробелы в начале";
  • Чтобы удалить конечные пробелы, выберите опцию "Пробелы в конце";
  • Чтобы удалить начальные и конечные пробелы одновременно, выберите опцию "Пробелы в начале и в конце";
  • Чтобы удалить все лишние пробелы, выберите опцию "Все лишние пробелы";
  • Чтобы удалить все пробелы, выберите опцию "Все пробелы".

3. Затем нажмите кнопку "Ок" или "Применить" — вы получите нужный результат.

Kutools для Excel - Усильте Excel более чем 300 необходимыми инструментами. Наслаждайтесь постоянно бесплатными функциями ИИ! Получите прямо сейчас

Удалить символы / текст до или после определенного символа

В этом разделе представлены способы удаления текста или символов до или после первого, последнего или N-го вхождения определенного символа.

3.1 Удалить текст до или после первого определенного символа

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

Удалить текст до первого определенного символа с помощью формулы

Чтобы удалить текст или символы до первого определенного символа, создайте формулу на основе функций RIGHT, LEN и FIND. Общий синтаксис:

"=RIGHT(cell, LEN(cell)-FIND("char", cell))"
  • "cell": Ссылка на ячейку или текстовая строка, из которой нужно удалить текст;
  • "char": Определенный разделитель, по которому нужно удалить текст.

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

=RIGHT(A2,LEN(A2)-FIND(",",A2))

Примечание: В приведенной выше формуле: "A2" — ячейка, из которой нужно удалить текст; "," — определенный символ, по которому нужно удалить текст (можно заменить на любой другой символ по необходимости).

Удалить текст после первого определенного символа с помощью формулы

Чтобы удалить все после первого определенного символа, используйте функции LEFT и FIND для получения результата. Общий синтаксис:

=LEFT(cell,FIND("char",cell)-1)
  • "cell": Ссылка на ячейку или текстовая строка, из которой нужно удалить текст;
  • "char": Определенный разделитель, по которому нужно удалить текст.

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

=LEFT(A2,FIND(",",A2)-1)

3.2 Удалить текст до или после N-го вхождения символа

Иногда в текстовых строках встречается несколько одинаковых разделителей, и может понадобиться удалить все символы до или после определенного вхождения, например, второго, третьего или четвертого. Для этого используйте следующие способы:

Удалить текст до N-го вхождения символа с помощью формулы

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

=RIGHT(cell,LEN(cell)-FIND("#",SUBSTITUTE(cell,"char","#",N)))
  • "cell": Ссылка на ячейку или текстовая строка, из которой нужно удалить текст;
  • "char": Определенный разделитель, по которому нужно удалить текст;
  • "N": Вхождение символа, до которого нужно удалить текст.

Например, чтобы удалить все до второй запятой из текстовых строк, примените следующую формулу:

=RIGHT(A2,LEN(A2)-FIND("#",SUBSTITUTE(A2,",","#",2)))

Примечание: В приведенной выше формуле: "A2" — ячейка, из которой нужно удалить текст; "," — определенный символ, по которому нужно удалить текст (можно заменить на любой другой символ); "2" — номер вхождения, до которого нужно удалить текст.

Затем протяните маркер заполнения для копирования формулы в другие ячейки, см. скриншот:

Удалить текст после N-го вхождения символа с помощью формулы

Чтобы удалить текст после N-го вхождения определенного разделителя, используйте функции LEFT, SUBSTITUTE и FIND. Общий синтаксис:

=LEFT(cell, FIND("#", SUBSTITUTE(cell, "char", "#", N)) -1)
  • "cell": Ссылка на ячейку или текстовая строка, из которой нужно удалить текст;
  • "char": Определенный разделитель, по которому нужно удалить текст;
  • "N": Вхождение символа, после которого нужно удалить текст.

Когда вы освоите базовый синтаксис, скопируйте или введите следующую формулу в пустую ячейку:

=LEFT(A2, FIND("#", SUBSTITUTE(A2, ",", "#", 2)) -1)

Примечание: В приведенной выше формуле: "A2" — ячейка, из которой нужно удалить текст; "," — определенный символ, по которому нужно удалить текст (можно заменить на любой другой символ); "2" — номер вхождения, после которого нужно удалить текст.

Затем протяните маркер заполнения для копирования формулы в другие ячейки — все символы после второй запятой будут удалены сразу, см. скриншот:

Удалить текст до или после N-го вхождения символа с помощью пользовательской функции

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

1. Удерживайте клавиши "Alt + F11", чтобы открыть окно "Microsoft Visual Basic for Applications".

2. Нажмите "Вставка" > "Модуль" и вставьте следующий код в окно модуля.

VBA-код: Удалить текст до или после N-го вхождения символа

Function RemoveTextOccurrence(Str As String, Delimiter As String, Occurrence As Integer, IsAfter As Boolean)
Dim xStr As String
Dim xStrLen, xF, xIntStart As Integer
xStr = Str
xStrLen = Len(xStr)
xIntStart = 1
For xF = 1 To Occurrence
xIntStart = InStr(xIntStart + 1, xStr, Delimiter, vbTextCompare)
If (xIntStart = 0) Or (xIntStart < 0) Then
    If IsAfter Then
    RemoveTextOccurrence = xStr
    Else
    RemoveTextOccurrence = ""
    End If
    Exit Function
End If
Next
If IsAfter Then
    RemoveTextOccurrence = Mid(Str, 1, xIntStart - 1)
Else
    RemoveTextOccurrence = Mid(Str, xIntStart + 1)
End If
End Function

3. Затем закройте и выйдите из окна кода, вернитесь на лист и используйте следующие формулы:

Удалить текст до второго вхождения запятой:

=RemoveTextOccurrence(A2, ", ", 2, FALSE)

A screenshot showing the result of using the RemoveTextOccurrence function in Excel to remove text before the second occurrence of a comma

Удалить текст после второго вхождения запятой

=RemoveTextOccurrence(A2, ", ", 2, TRUE)

A screenshot showing the result of using the RemoveTextOccurrence function in Excel to remove text after the second occurrence of a comma

3.3 Удалить текст до или после последнего вхождения символа

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

Удалить текст до последнего вхождения символа с помощью формулы

Чтобы удалить все символы до последнего вхождения символа, используйте следующий общий синтаксис:

=RIGHT(cell,LEN(cell)-SEARCH("#",SUBSTITUTE(cell,"char","#",LEN(cell)-LEN(SUBSTITUTE(cell,"char","")))))
  • "cell": Ссылка на ячейку или текстовая строка, из которой нужно удалить текст;
  • "char": Определенный разделитель, по которому нужно удалить текст;

Если нужно удалить текст до последнего вхождения запятой, скопируйте или введите следующую формулу в пустую ячейку:

=RIGHT(A2,LEN(A2)-SEARCH("#",SUBSTITUTE(A2,",","#",LEN(A2)-LEN(SUBSTITUTE(A2,",","")))))

Примечание: В приведенной выше формуле: "A2" — ячейка, из которой нужно удалить текст; "," — определенный символ, по которому нужно удалить текст (можно заменить на любой другой символ).

Затем протяните маркер заполнения для копирования формулы в другие ячейки — все символы до последней запятой будут удалены, как показано на скриншоте ниже:

Удалить текст после последнего вхождения символа с помощью формул

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

=LEFT(cell,FIND("#",SUBSTITUTE(cell,"char","#",LEN(cell)-LEN(SUBSTITUTE(cell,"char",""))))-1)
  • "cell": Ссылка на ячейку или текстовая строка, из которой нужно удалить текст;
  • "char": Определенный разделитель, по которому нужно удалить текст;

Скопируйте или введите следующую формулу в пустую ячейку и протяните маркер заполнения вниз для получения других результатов, см. скриншот:

=LEFT(A2,FIND("#",SUBSTITUTE(A2,",>","#",LEN(A2)-LEN(SUBSTITUTE(A2,",",""))))-1)

Примечание: В приведенной выше формуле: "A2" — ячейка, из которой нужно удалить текст; "," — определенный символ, по которому нужно удалить текст (можно заменить на любой другой символ).

3.4 Удалить текст между скобками

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

Удалить текст между скобками с помощью функции Поиск и Замена

В Excel встроенная функция Поиск и Замена поможет найти все тексты в скобках и заменить их на пустое значение. Выполните следующие действия:

1. Выделите список данных, из которого нужно удалить текст между скобками.

2. Затем нажмите "Главная" > "Найти и выделить" > "Заменить" (или нажмите клавиши "Ctrl + H"), чтобы открыть диалоговое окно Поиск и Замена, и выполните следующие действия:

  • В поле "Найти" введите "(*)";
  • В поле "Заменить на" оставьте это поле пустым.

3. Затем нажмите кнопку "Заменить все" — все символы в скобках (включая скобки) в выбранных ячейках будут удалены сразу. См. скриншот:

Совет: Функция Поиск и Замена также работает для двух и более пар скобок в текстовых строках.

Удалить текст между скобками с помощью формулы

Кроме функции Поиск и Замена, можно использовать формулу для решения задачи в Excel. Общий синтаксис:

=SUBSTITUTE(text,MID(LEFT(text,FIND(")",text)),FIND("(",text),LEN(text)),"")
  • "text": Текстовая строка или ссылка на ячейку, из которой нужно удалить символы.

Скопируйте или введите следующую формулу в пустую ячейку, где хотите получить результат:

=SUBSTITUTE(A2,MID(LEFT(A2,FIND(")",A2)),FIND("(",A2),LEN(A2)),"")

Затем протяните маркер заполнения вниз до нужных ячеек — все тексты в скобках, включая скобки, будут удалены сразу, см. скриншот:

Совет: Если в значении ячейки нет скобок, после применения формулы выше появится ошибка. Чтобы игнорировать ошибку, используйте формулу ниже:

=IFERROR(SUBSTITUTE(A2,MID(LEFT(A2,FIND(")",A2)),FIND("(",A2),LEN(A2)),""),A2)

Удалить текст между скобками с помощью пользовательской функции

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

1. Удерживайте клавиши "Alt + F11", чтобы открыть окно "Microsoft Visual Basic for Applications".

2. Нажмите "Вставка" > "Модуль" и вставьте следующий код в окно модуля.

VBA-код: Удалить текст между скобками

Function remtxt(ByVal str As String) As String
'Updateby Extendoffice
  While InStr(str, "(") > 0 And InStr(str, ")") > InStr(str, "(")
    str = Left(str, InStr(str, "(") - 1) & Mid(str, InStr(str, ")") + 1)
  Wend
  remtxt = Trim(str)
End Function

3. Вернитесь на лист, введите формулу: "=remtxt(A2)" в пустую ячейку, затем протяните маркер заполнения вниз для применения формулы. Весь текст в скобках, включая сами скобки, будет удален, как показано на скриншоте:

A screenshot showing the result of using a custom VBA function in Excel to remove text between multiple pairs of parentheses

Удалить слова из текстовых строк

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

4.1 Удалить первое или последнее слово из текстовой строки

Чтобы удалить первое или последнее слово из списка текстовых строк, воспользуйтесь следующими формулами.

Удалить первое слово из текстовой строки с помощью формулы

Для удаления первых слов из списка текстовых строк создайте простую формулу на основе функций RIGHT, LEN и FIND. Общий синтаксис:

=RIGHT(text,LEN(text)-FIND(" ",text))
  • "text": Текстовая строка или ссылка на ячейку, из которой нужно удалить первое слово.

Введите или скопируйте следующую формулу в пустую ячейку:

=RIGHT(A2,LEN(A2)-FIND(" ",A2))

Затем протяните маркер заполнения вниз для применения формулы к другим ячейкам, см. скриншот:

Совет: Если нужно удалить первые N слов из ячеек, используйте формулу ниже:

=MID(TRIM(text),1+FIND("~",SUBSTITUTE(TRIM(text)," ","~",N)),255)
  • "text": Текстовая строка или ссылка на ячейку, из которой нужно удалить первые n слов;
  • "N": Количество слов, которые нужно удалить с начала текстовой строки.

Например, чтобы удалить первые два слова из ячеек, скопируйте или введите следующую формулу в пустую ячейку для получения результата, см. скриншот:

=MID(TRIM(A2),1+FIND("~",SUBSTITUTE(TRIM(A2)," ","~",2)),255)

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

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

=LEFT(TRIM(text),FIND("~",SUBSTITUTE(text," ","~",LEN(TRIM(text))-LEN(SUBSTITUTE(TRIM(text)," ",""))))-1)
  • "text": Текстовая строка или ссылка на ячейку, из которой нужно удалить последнее слово;

Используйте следующую формулу в пустой ячейке и протяните маркер заполнения вниз для применения формулы к другим ячейкам, см. скриншот:

=LEFT(TRIM(A2),FIND("~",SUBSTITUTE(A2," ","~",LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))))-1)

Совет: Чтобы удалить последние N слов из списка ячеек, используйте следующий общий синтаксис:

=LEFT(text,FIND("~",SUBSTITUTE(text," ","~",LEN(text)-LEN(SUBSTITUTE(text," ",""))-(N-1))))
  • "text": Текстовая строка или ссылка на ячейку, из которой нужно удалить последние n слов;
  • "N": Количество слов, которые нужно удалить с конца текстовой строки.

Например, чтобы удалить последние3 слова из списка ячеек, используйте следующую формулу для получения результата, см. скриншот:

=LEFT(A2,FIND("~",SUBSTITUTE(A2," ","~",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-(3-1))))

4.2 Удалить дублирующиеся символы или слова внутри ячейки

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

Удалить дублирующиеся символы внутри ячейки с помощью пользовательской функции

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

1. Удерживайте клавиши "Alt + F11", чтобы открыть окно "Microsoft Visual Basic for Applications".

2. Нажмите "Вставка" > "Модуль" и вставьте следующий код в окно модуля.

VBA-код: Удалить дублирующиеся символы внутри ячейки

Function RemoveDupeschars(pWorkRng As Range) As String
'Updateby Extendoffice
Dim xValue As String
Dim xChar As String
Dim xOutValue As String
Set xDic = CreateObject("Scripting.Dictionary")
xValue = pWorkRng.Value
For i = 1 To VBA.Len(xValue)
    xChar = VBA.Mid(xValue, i, 1)
    If xDic.Exists(xChar) Then
    Else
        xDic(xChar) = ""
        xOutValue = xOutValue & xChar
    End If
Next
RemoveDupeschars = xOutValue
End Function

3. Затем закройте окно кода, вернитесь на лист и введите формулу =RemoveDupeschars(A2) в пустую ячейку рядом с вашими данными, затем протяните маркер заполнения до нужных ячеек, см. скриншот:

Примечание: "A2" — ячейка, из которой нужно удалить дублирующиеся символы.

A screenshot showing the formula being applied in Excel to remove duplicate characters from a text string in a cell

Совет: Функция чувствительна к регистру, то есть различает строчные и прописные буквы.

Удалить дублирующиеся слова внутри ячейки с помощью пользовательской функции

Если в ячейке есть одинаковые слова или текстовые строки, и вы хотите удалить все одинаковые слова, как показано на скриншоте ниже, используйте следующую пользовательскую функцию для решения задачи в Excel.

1. Удерживайте клавиши "Alt + F11", чтобы открыть окно "Microsoft Visual Basic for Applications".

2. Нажмите "Вставка" > "Модуль" и вставьте следующий код в окно модуля.

VBA-код: Удалить дублирующиеся слова внутри ячейки

Function RemoveDupeswords(txt As String, Optional delim As String = " ") As String
'Updateby Extendoffice
    Dim x
    With CreateObject("Scripting.Dictionary")
        .CompareMode = vbTextCompare
        For Each x In Split(txt, delim)
            If Trim(x) <> "" And Not .exists(Trim(x)) Then .Add Trim(x), Nothing
        Next
        If .Count > 0 Then RemoveDupeswords = Join(.keys, delim)
    End With
End Function

3. Затем закройте окно кода, вернитесь на лист и введите формулу =RemoveDupeswords(A2,", ") в пустую ячейку рядом с вашими данными, затем протяните маркер заполнения до нужных ячеек, см. скриншот:

Примечание: "A2" — ячейка, из которой нужно удалить дублирующиеся слова, а запятая с пробелом (", ") — разделитель между текстовыми строками (можно заменить на любой другой разделитель по необходимости).

A screenshot showing the formula being applied in Excel to remove duplicate words from a text string in a cell

Совет: Эта функция не чувствительна к регистру, строчные и прописные буквы считаются одинаковыми символами.

4.3 Обрезать текстовую строку до N слов

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

Обрезать текстовую строку до N слов с помощью формулы

Чтобы обрезать текстовую строку до N слов, создайте формулу на основе функций LEFT, FIND и SUBSTITUTE. Общий синтаксис:

=LEFT(text,FIND("~",SUBSTITUTE(text," ","~",N))-1)
  • "text": Текстовая строка или ссылка на ячейку, которую нужно обрезать;
  • "N": Количество слов, которые нужно оставить слева от заданной текстовой строки.

Для выполнения задачи скопируйте или введите следующую формулу в пустую ячейку:

=LEFT(A2,FIND("~",SUBSTITUTE(A2," ","~",B2))-1)

Затем протяните маркер заполнения вниз для применения формулы к другим ячейкам, см. скриншот:

Обрезать текстовую строку до N слов с помощью пользовательской функции

Кроме формулы выше, можно создать пользовательскую функцию для решения задачи, выполните следующие действия:

1. Удерживайте клавиши "Alt + F11", чтобы открыть окно "Microsoft Visual Basic for Applications".

2. Нажмите "Вставка" > "Модуль" и вставьте следующий код в окно модуля.

VBA-код: Обрезать текстовую строку до N слов

Function GetNWords(StrWords As String, Num_of_Words As Integer) As String
'Updateby Extendoffice
Dim xArr
Dim xRes As String
Dim xF As Integer
xStr = StrWords
If (Num_of_Words < 1) Then
    GetNWords = ""
    Exit Function
End If
xArr = Split(xStr, " ")
xRes = ""
On Error Resume Next
For xF = 0 To UBound(xArr)
    If Trim(xArr(xF)) <> "" Then
    Num_of_Words = Num_of_Words - 1
        If xRes = "" Then
            xRes = Trim(xArr(xF))
        Else
            xRes = xRes & " " & Trim(xArr(xF))
        End If
    End If
    If Num_of_Words = 0 Then Exit For
Next
If Num_of_Words = 0 Then
    GetNWords = xRes & "..."
Else
    GetNWords = xRes & "..."
End If
End Function

3. Затем закройте и выйдите из окна кода, вернитесь на лист и введите формулу: =GetNWords(A2,B2) в пустую ячейку, затем протяните маркер заполнения вниз для применения формулы к другим ячейкам — останутся только первые заданные слова, как показано на скриншоте ниже:

A screenshot showing the result after applying the User Defined Function to trim the text string to a specific number of words in Excel

Лучшие инструменты для повышения продуктивности в Office

🤖 Kutools AI Aide: Совершенно новый подход к анализу данных благодаря: Интеллектуальное выполнение |  Генерация кода  |  Создание пользовательских формул |  Анализ данных и построение диаграмм  |  Вызов Kutools Functions…
Популярные функции: Поиск, выделение или отметка дубликатов | Удалить пустые строки | Объединить столбцы или адреса без потери данных | Округлить ...
Супер ПОИСК: VLOOKUP по нескольким критериям | VLOOKUP по нескольким значениям | Многолистовой поиск | Распознавание нечетких соответствий ...
Расширенный раскрывающийся список: Быстро создать раскрывающийся список | Зависимый раскрывающийся список | Множественный выбор в раскрывающемся списке ...
Менеджер столбцов: Добавить определённое количество столбцов | Переместить столбцы | Переключить видимость скрытых столбцов | Сравнить диапазоны и столбцы ...
Рекомендуемые функции: Сетка фокусировки | Дизайн листа | Улучшенная строка формулы | Управление книгой и листами | Библиотека автотекста | Выбор даты | Объединить данные | Зашифровать/расшифровать ячейки | Отправить письмо по списку | Супер фильтр | Специальный фильтр (фильтр жирный/курсив/зачеркнутый...) ...
Топ15 наборов инструментов:12 текстовых инструментов (Добавить текст, Удалить определенные символы, ...) |50+ типов диаграмм (Диаграмма Ганта, ...) |40+ полезных формул (Расчет возраста на основе даты рождения, ...) |19 инструментов для вставки (Вставить QR-код, Вставить изображение по пути, ...) |12 инструментов преобразования (Преобразовать в слова, Конвертация валюты, ...) |7 инструментов объединения и разделения (Расширенное объединение строк, Разделить ячейки, ...) | ... и многое другое
Используйте Kutools на предпочитаемом вами языке — поддерживает Английский, Испанский, Немецкий, Французский, Китайский и более40 других языков!

Повысьте свои навыки работы в 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 Tabs PowerPoint
  • Комплексный набор — надстройки для Excel, Word, Outlook и PowerPoint плюс Office Tab Pro
  • Один установщик, одна лицензия — настройка занимает считанные минуты (MSI-совместимо)
  • Совместная работа — максимальная эффективность между приложениями Office
  • 30-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
  • Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек