|
КАК?! Разбить ячейку на символы? |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
В этом руководстве объясняется, как разделить строку текста по определенному символу на отдельные ячейки столбца с формулами в Excel.
Как разделить текстовую строку на определенный символ в ячейке в Excel?
Предположим, у вас есть список текстовых строк в столбце B, как показано на снимке экрана ниже, и вы хотите разделить текст по символу «-» и вывести их в трех отдельных столбцах. В этом случае приведенные ниже формулы могут оказать вам услугу.
Общие формулы
Общая формула для разделения левого текста
=LEFT(text_string,FIND(«Char», text_string)-1)
Общая формула для разделения среднего текста
=MID(text_string, FIND(«Char», text_string) + 1, FIND(«Char», text_string,FIND(«Char», text_string)+1) — FIND(«Char», text_string) — 1)
Общая формула для разделения правого текста
=RIGHT(text_string,LEN(text_string) — FIND(«Char», text_string, FIND(«Char», text_string) + 1))
Аргументы приведенных выше формул
Текстовая_строка: Текстовая строка, из которой вы хотите отделить текст;
Голец: Символ, на основе которого вы разделите текст.
Как пользоваться этими формулами?
Во-первых, разделите первую текстовую строку на основе определенного символа
1. Выберите пустую ячейку для вывода результата. Здесь я выбираю C3.
2. Введите в нее приведенную ниже формулу и нажмите Enter ключ. А затем перетащите ячейку с ЛЕВОЙ формулой полностью вниз, чтобы применить ее к другим ячейкам.
=LEFT(B3,FIND(«-«,B3)-1)
Во-вторых, разделите среднюю текстовую строку на основе определенного символа
1. Выберите пустую ячейку, например D3, для вывода результата.
2. Введите в нее приведенную ниже формулу и нажмите Enter ключ. Выберите ячейку результата, перетащите маркер заполнения вниз, чтобы применить формулу к другим ячейкам.
=MID(B3, FIND(«-«,B3) + 1, FIND(«-«,B3,FIND(«-«,B3)+1) — FIND(«-«,B3) — 1)
Наконец, разделите последнюю текстовую строку на основе определенного символа
1. Выберите пустую ячейку, например E3, для вывода результата.
2. Введите в нее приведенную ниже формулу и нажмите Enter ключ. Выберите ячейку результата, перетащите маркер заполнения вниз, чтобы применить формулу к другим ячейкам.
=RIGHT(B3,LEN(B3) — FIND(«-«, B3, FIND(«-«, B3) + 1))
Теперь текстовая строка в каждой ячейке столбца B разбита на три части.
Tips: Если вы хотите разделить текстовую строку только на две части на основе определенного символа, после разделения первой части с помощью приведенной выше формулы LEFT вы можете применить приведенную ниже формулу RIGHT, чтобы извлечь вторую часть, как показано на скриншоте ниже.
=RIGHT(B3,LEN(B3)-FIND(«-«,B3))
Как работают эти формулы?
=LEFT(B3,FIND(«-«,B3)-1)
- FIND(«-«,B3): Функция НАЙТИ возвращает начальную позицию символа «-» в текстовой строке KTE-Jan-359. Результат — 4.
- LEFT(B3,4-1): Функция LEFT извлекает 3 символа (4-1 = 3) из левой части текстовой строки KTE-Jan-359. Вот результат — КТЕ.
Внимание: Поскольку первый символ «-» находится в 4-й позиции текстовой строки, вам необходимо вычесть 1, чтобы исключить его из извлечения.
=MID(B3, FIND(«-«,B3) + 1, FIND(«-«,B3,FIND(«-«,B3)+1) — FIND(«-«,B3) — 1)
- FIND(«-«,B3) + 1: Функция НАЙТИ возвращает начальную позицию символа «-» в текстовой строке KTE-Jan-359. Здесь результат 4.
Внимание: Поскольку первый символ «-» находится в 4-й позиции текстовой строки, вам нужно добавить от 1 до 4, чтобы извлечь текст после него, и, наконец, мы заканчиваем с 4 + 1 = 5. - FIND(«-«,B3,FIND(«-«,B3)+1) здесь отображается как НАЙТИ («-», KTE-Jan-359,4 + 1): Функция «Найти» находит позицию символа «-», начиная с 5-го символа в KTE-Jan-359. И возвращает результат как 8.
- MID(B3, 5, 8 — 4 — 1): MID (KTE-Jan-359,5,3), что означает, что функция MID извлекает 3 символа, начиная с 5-го символа в текстовой строке KTE-Jan-359. И результат Янв.
=RIGHT(B3,LEN(B3) — FIND(«-«, B3, FIND(«-«, B3) + 1))
- LEN(B3): Функция LEN вычисляет общую длину текстовой строки KTE-Jan-359 и возвращает результат как 11.
- FIND(«-«,B3,FIND(«-«,B3)+1): Функция поиска находит позицию символа «-«, начиная с 5-го символа в KTE-Jan-359. И возвращает результат как 8.
- RIGHT(B3,11 — 8)): Функция ВПРАВО извлекает 3 (11-8) символа из правой части KTE-Jan-359. Здесь результат 359.
Связанные функции
Функция ВЛЕВО в Excel
Функция LEFT извлекает заданное количество символов из левой части предоставленной строки.
Функция поиска в Excel
Функция НАЙТИ используется для поиска строки в другой строке и возвращает начальную позицию строки внутри другой.
Функция Excel MID
Функция MID используется для поиска и возврата определенного количества символов из середины данной текстовой строки.
Функция Excel ВПРАВО
Функция RIGHT извлекает определенное количество символов из правой части текстовой строки.
Функция ДЛСТР в Excel
Функция Excel LEN возвращает количество символов в текстовой строке.
Родственные формулы
Лучшие инструменты для работы в офисе
Kutools for Excel — Помогает вам выделиться из толпы
Хотите быстро и качественно выполнять свою повседневную работу? Kutools for Excel предлагает 300 мощных расширенных функций (объединение книг, суммирование по цвету, разделение содержимого ячеек, преобразование даты и т. д.) и экономит для вас 80 % времени.
- Разработан для 1500 рабочих сценариев, помогает решить 80% проблем с Excel.
- Уменьшите количество нажатий на клавиатуру и мышь каждый день, избавьтесь от усталости глаз и рук.
- Станьте экспертом по Excel за 3 минуты. Больше не нужно запоминать какие-либо болезненные формулы и коды VBA.
- 30-дневная неограниченная бесплатная пробная версия. 60-дневная гарантия возврата денег. Бесплатное обновление и поддержка 2 года.
Вкладка Office — включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)
- Одна секунда для переключения между десятками открытых документов!
- Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
- Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Добавляет эффективные вкладки в Office (включая Excel), точно так же, как Chrome, Firefox и новый Internet Explorer.
In this guide, we’re going to show you how to split text in Excel by a delimiter.
Download Workbook
We have a sample data which contains concatenated values separated by “|” characters. It is important that the data includes a specific delimiter character between each chunk of data to make splitting text easier.
Text to Columns feature for splitting text
When splitting text in Excel, the Text to Columns is one of the most common methods. You can use the Text to Columns feature with all versions of Excel. This feature can split text by a specific character count or delimiter.
- Start with selecting your data. You can use more than one cell in a column.
- Click Data > Text to Columns in the Ribbon.
- On the first step of the wizard, you have 2 options to choose from — these are slicing methods. Since our data in this example is split by delimiters, our choice is going to be Delimited.
- Click Next to continue
- Select the delimiters suitable to your data or choose a character length and click Next.
- Choose corresponding data types for the columns and the destination cell. Please note that if the destination cell is the same cell as where your data is, the original data will be overwritten.
- Click Finish to see the outcome.
Using formulas to split text
Excel has a variety of text formulas that you can use to locate delimiters and parse data. When using formulas to do so, Excel automatically updates the parsed values when the source is updated.
The formula used in this example uses Microsoft 365’s dynamic array feature, which allows you to populate multiple cells without using array formulas. If you can see the SEQUENCE formula, you can use this method.
Syntax
=TRIM(MID(SUBSTITUTE( text, separator, REPT( “ “, LEN( text ))),(SEQUENCE( 1, column count ) — 1 ) * LEN( text ) + 1,LEN( text )))
How it works
The formula replaces each separator character with space characters first. (see SUBSTITUTE) The number of space characters will be equal to the original string’s character count, which is enough number of spaces to separate each string block (see REPT and LEN).
The SEQUENCE function generates an array of numbers starting with 1, up to the number of maximum columns. Multiplying these sequential numbers with the length of the original string returns character numbers indicating the start of each block.
This approach uses the MID function to parse each string block with given start character number and the number of characters to return. Since the separators are replaced with space characters, each parsed block includes these spaces around the actual string. The TRIM function then removes these spaces.
Flash Fill
The Flash Fill is an Excel tool which can detect the pattern when entering data. A common example is to separate or merge first name and last names. Let’s say column A contains the first name — last name combinations. If you type in the corresponding first name in the same row for column B, Excel shows you a preview for the rest of the column.
Please note that the Flash Fill is available for Excel 2013 and newer versions only.
You can see the same behavior with strings using delimiters to separate data. Just select a cell in the adjacent column and start typing. Occasionally Excel will display a preview. If not, press Ctrl + E like below to split your text.
Power Query
Power Query is a powerful feature, not only for splitting text, but for data management in general. Power Query comes with its own text splitting tool which allows you to split text in multiple ways, like by delimiters, number of characters, or case of letters.
If you are using Excel 2016 or newer — including Microsoft 365 — you can find Power Query options under the Data tab’s Get & Transform section. Excel 2010 and 2013 users should download and install the Power Query as an add-in.
- Select the cells containing the text.
- Click Data > From Sheet. If the data is not in an Excel Table, Excel converts it into an Excel Table first.
- Once the Power Query window is open, find the Split Column under the Transform tab and click to see the options.
- Select the approach that fits your data layout. The data in our example is using By Delimiter since the data is separated by “|”.
- Power Query will show the delimiter character. If you are not seeing the expected delimiter, choose from the list or enter it yourself.
- Click OK to split text.
- If your data includes headers in its first row, like our example does, click Transform > Use First Row as Headers to keep them as headers.
- One you satisfied with the result, click the Home > Close & Load button to move the split data into your workbook.
VBA
VBA is the last text splitting option we want to show in this article. You can use VBA in two ways to split text:
- By calling the Text to Columns feature using VBA,
- Using VBA’s Split function to create array of sub-strings and using a loop to distribute the sub-strings into adjacent cells in the same row.
Text to Columns by VBA
The following code can split data from selected cells into the adjacent columns. Note that each supported delimiter is listed as an argument which you can enable or disable by giving them either True or False. Briefly, True means that you want to set that argument as a delimiter and False means ignore that character.
An easy way to generate a VBA code to split text is to record a macro. Start a recording section using the Record Macro button in the Developer tab, and use the Text to Columns feature. Once the recording is stopped, Excel will save the code for what you did during recording.
The code:
Sub VBATextToColumns_Multiple()
Dim MyRange As Range
Set MyRange = Selection ‘To work on a static range replace Selection via Range object, e.g., Range(“B6:B12”)
MyRange.TextToColumns _
Destination:=MyRange(1, 1).Offset(, 1), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
SemiColon:=False, _
Comma:=False, _
Space:=False, _
Other:=True, _
OtherChar:=»|»
End Sub
Split Function
Split function can be useful if you want to keep the split blocks in an array. You can only use this method for splitting because of the single delimiter constraint. The following code loops through each cell in a selected column, splits and stores text by the delimiter “|”, and uses another loop to populate the values in the array on cells. The final EntireColumn.AutoFit command adjusts the column widths.
The code:
Sub SplitText()
Dim StringArray() As String, Cell As Range, i As Integer
For Each Cell In Selection ‘To work on a static range replace Selection via Range object, e.g., Range(“B6:B12”)
StringArray = Split(Cell, «|») ‘Change the delimiter with a character suits your data
For i = 0 To UBound(StringArray)
Cell.Offset(, i + 1) = StringArray(i)
Cell.Offset(, i + 1).EntireColumn.AutoFit ‘This is for column width and optional.
Next i
Next
End Sub
На чтение 12 мин. Просмотров 18.3k.
Функция VBA InStr является одной из наиболее часто используемых функций в VBA. Он используется для нахождения текста внутри строки и действительно отлично справляется с работой.
Тем не менее, она часто используется, чтобы помочь извлечь часть строки, и эту задачу она выполняет плохо.
Если вы обнаружили, что извлечение текста в VBA является болезненным процессом, тогда читайте дальше. Эта статья покажет вам более простой и лучший способ, используя три реальных примера!
Содержание
- Краткое руководство к статье
- Краткая справка
- Введение
- Когда VBA InStr, Left, Right и Mid полезны
- Работа со строками различной длины
- Использование функции VBA InStr с Mid
- Функция Split
- Пример 1: Получение части имени файла
- Пример 2: диапазон IP-адресов
- Пример 3. Проверьте правильность имени файла
- Заключение
Краткое руководство к статье
В следующей таблице приведено краткое руководство к тому, что рассматривается в этой статье.
| Строка | Тип | Задача | Как |
| 1234ABC334 | Фиксированный размер | Оставить слева 4 символа | Left(s,4) |
| 1234ABC334 | Фиксированный размер | Оставить справа 3 символа |
Right(s,3) |
| 1234ABC334 | Фиксированный размер | Оставить 5, 6, 7 символы | Mid(s,5,3) |
| «Иван Петрович Сидоров» |
Переменный размер |
Оставить имя | Split(s,» «)(0) |
| «Иван Петрович Сидоров» |
Переменный размер |
Оставить отчество |
Split(s,» «)(1) |
| «Иван Петрович Сидоров» |
Переменный размер |
Оставить фамилию |
Split(s,» «)(2) |
| «Иван Петрович Сидоров» |
Переменный размер |
Оставить фамилию |
Dim v As Variant v = Split(s, » «) lastname= v(UBound(v)) |
Краткая справка
Чтобы узнать больше об элементах, упомянутых в статье, перейдите по следующим ссылкам:
- Если вы хотите узнать больше о функциях InStr или InStrRev, пожалуйста, прочитайте Поиск в строке.
- Если вы хотите узнать больше о функциях Mid, Left или Right, посмотрите раздел Извлечение части строки.
- Для получения дополнительной информации о функции Split проверьте Строка в массив, используя Split.
- Оператор Like включен в Сравнение строк с шаблоном
Я использую Debug.Print в моих примерах. Он печатает значения в Immediate Window, которое вы можете просмотреть, нажав Ctrl + G (или выберите View-> Immediate Window)
Введение
В этой статье я собираюсь показать вам лучший способ извлечения символов из строки, чем использование функции VBA InStr с Left, Right или Mid.
Эта статья разбита следующим образом:
- Раздел 1: Как извлечь из строк фиксированного размера.
- Раздел 2: Как извлечь из строк переменного размера.
- Раздел 3: Как извлечь из строки переменного размера, используя функцию Split.
- Разделы с 4 по 6: некоторые примеры из реальной жизни.
Когда VBA InStr, Left, Right и Mid полезны
Если вы хотите проверить, содержит ли строка значение, InStr подходит для этой работы. Если вы хотите сделать простое извлечение, то отлично подойдут Left, Right и Mid.
Использование InStr для проверки, содержит ли строка текст
В следующем примере мы проверяем, содержит ли ФИО «Петрович». Если возвращаемое значение InStr больше нуля, то строка содержит значение, которое мы проверяем.
' Проверьте, содержит ли строка Петрович
If InStr("Иван Петрович Сидоров", "Петрович") > 0 Then
Debug.Print "Найдено"
End If
Извлечение с Left, Right и Mid
Функция Left используется для получения символов слева от строки.
Функция Right используется для получения символов справа от строки.
Функция Mid используется для середины строки. Она такая же, как
Left, за исключением того, что вы даете ему стартовую позицию.
Sub IzvlechTekst()
Dim s As String: s = "ABCD-7789.WXYZ"
Debug.Print Left(s, 2) ' Печатает AB
Debug.Print Left(s, 4) ' Печатает ABCD
Debug.Print Right(s, 2) ' Печатает YZ
Debug.Print Right(s, 4) ' Печатает WXYZ
Debug.Print Mid(s, 1, 2) ' Печатает AB
Debug.Print Mid(s, 6, 4) ' Печатает 7789
End Sub
Эти три функции работают нормально, если требуемый текст всегда одинакового размера и в одном и том же месте. Для других сценариев они требуют использования InStr, чтобы найти определенную позицию в строке. Это усложняет их использование.
Используйте Left, Right или Mid, когда символы всегда будут в одной и той же позиции.
Работа со строками различной длины
Многие из строк, с которыми вы имеет дело, разной длины. Простой пример — когда у вас есть дело со списком имен. Длина строки и требуемая часть (например, имя) могут каждый раз отличаться. Например:
Brooke Hilt
Pamela Jurado
Zack Kinzel
Eddy Wormley
Kaitlyn Rainer
Jacque Trickett
Kandra Stanbery
Margo Hoppes
Berenice Meier
Garrett Hyre
(Если вам нужен случайный список имен, попробуйте этот генератор случайных имен)
Использование функции VBA InStr с Left
В следующем примере мы собираемся получить имя из строки. В этой строке первое имя — это имя перед первым пробелом.
Мы используем функцию VBA InStr, чтобы получить позицию первого пробела. Мы хотим получить все символы до пробела. Мы вычитаем одну из позиции, так как это дает нам позицию последней буквы имени.
Sub PoluchitImya()
Dim s As String, lPosition As Long
s = "John Henry Smith"
' Печатает John
lPosition = InStr(s, " ") - 1
Debug.Print Left(s, lPosition)
s = "Lorraine Huggard"
' Печатает Lorraine
lPosition = InStr(s, " ") - 1
Debug.Print Left(s, lPosition)
End Sub
Давайте посмотрим на первый пример в приведенном выше коде. Первый пробел находится в позиции 5. Мы вычтем 1, что дает нам позицию 4. Это позиция последней буквы John, т.е.
Затем мы даем 4 функции Left, и она возвращает первые четыре символа, например, «John»
Мы можем выполнить ту же задачу в одной строке, передав возвращаемое значение из InStr в функцию Left.
Dim s As String
s = "John Henry Smith"
' Печатает John
Debug.Print Left(s, InStr(s, " ") - 1)
Использование функции VBA InStr с Right
В этом примере мы получим последнее слово в строке, то есть Smith. Мы можем использовать функцию InStrRev. Это то же самое, что InStr, за исключением того, что поиск выполняется с конца строки.
Важно отметить, что InStrRev дает нам позицию с начала строки. Поэтому нам нужно использовать его немного иначе, чем мы использовали InStr и Left.
Sub PoluchitFamiliyu()
Dim s As String: s = "John,Henry,Smith"
Dim Position As Long, Length As Long
Position = InStrRev(s, ",")
Length = Len(s)
' Печатает Smith
Debug.Print Right(s, Length - Position)
' Альтернативный метод. Печатает Smith - делает в одну строку
Debug.Print Right(s, Len(s) - InStrRev(s, ","))
End Sub
Как работает приведенный выше пример:
- Мы получаем позицию последнего пробела, используя InStrRev: 11
- Мы получаем длину строки: 16.
- Вычитаем позицию из длины: 16-11 = 5
- Мы даем 5 функции Right и возвращаем Smith
Использование функции VBA InStr с Mid
В следующем примере мы получим «Henry» из строки. Слово, которое мы ищем, находится между первым и вторым пробелом.
Мы будем использовать функцию Mid здесь.
Sub PoluchitVtoroeImya()
Dim s As String: s = "John Henry Smith"
Dim firstChar As Long, secondChar As Long
Dim count As Long
' Найти пробел плюс 1. Результат 6
firstChar = InStr(s, " ") + 1
' Найти 2-й пробел. Результат 11
secondChar = InStr(firstChar, s, " ")
' Получить число символов. Результат 5
count = secondChar - firstChar
' Печатает Henry
Debug.Print Mid(s, firstChar, count)
End Sub
Как видите, это сложно сделать и требует немного усилий, чтобы выяснить. Нам нужно найти первое место. Тогда нам нужно найти второе место. Затем мы должны вычесть одно из другого, чтобы дать нам количество символов, которые нужно взять.
Если у вас есть строка с большим количеством слов, то это может быть очень сложно. К счастью для нас, гораздо проще было извлечь символы из строки. Это называется функцией Split.
Функция Split
Мы можем использовать функцию Split для выполнения приведенных выше примеров. Функция Split разбивает строку на массив. Тогда мы можем легко получить доступ к каждому элементу.
Давайте попробуем те же три примера еще раз, и на этот раз мы будем использовать Split.
Dim s As String: s = "John Henry Smith"
Debug.Print Split(s, " ")(0) ' John
Debug.Print Split(s, " ")(1) ' Henry
Debug.Print Split(s, " ")(2) ' Smith
Ого! Какая разница с использованием Split. Как это работает:
- Функция Split разбивает строку везде, где есть пробел.
- Каждый элемент помещается в массив, начиная с нуля.
- Используя номер местоположения, мы можем получить доступ к элементу массива.
В следующей таблице показано, как может выглядеть массив после использования Split.
Примечание: первая позиция в массиве равна нулю. Наличие нулевых массивов является стандартным в языках программирования.
| 0 | 1 | 2 |
| John | Henry | Smith |
В приведенном выше коде мы разделяем строку каждый раз, когда ее используем. Мы также можем разделить строку один раз и сохранить ее в переменной массива. Тогда мы можем получить к нему доступ, когда захотим.
Sub SplitName()
Dim s As String: s = "John Henry Smith"
Dim arr() As String
arr = Split(s, " ")
Debug.Print arr(0) ' John
Debug.Print arr(1) ' Henry
Debug.Print arr(2) ' Smith
End Sub
Если вы хотите узнать больше о массивах, я написал о них целую статью под названием «Полное руководство по использованию массивов в Excel VBA».
В следующих разделах мы рассмотрим примеры из реальной жизни. Вы увидите преимущество использования Split вместо функции InStr.
Пожалуйста, не стесняйтесь попробовать это сами. Это отличный способ учиться, и вы можете повеселиться, пытаясь понять их (или, может быть, только у меня так!)
Пример 1: Получение части имени файла
Представьте, что мы хотим извлечь числа из следующих имен файлов
«VB_23476_Val.xls»
«VV_987_Val.txt»
«VZZA_12223_Val.doc»
Это похоже на пример, где мы получаем второй элемент. Чтобы получить значения здесь, мы используем подчеркивание (то есть «_»), чтобы разбить строку. Смотрите пример кода ниже:
Sub PoluchitNomer()
' Печатает 23476
Debug.Print Split("VB_23476_Val.xls", "_")(1)
' Печатает 987
Debug.Print Split("VV_987_Val.txt", "_")(1)
' Печатает 12223
Debug.Print Split("ABBZA_12223_Val.doc", "_")(1)
End Sub
В реальном мире вы обычно читаете такие строки из разных ячеек. Допустим, эти имена файлов хранятся в ячейках от А1 до А3. Мы немного изменим приведенный выше код:
Sub ChitatNomera()
Dim c As Range
For Each c In Range("A1:A3")
' Разделите каждый элемент по мере его прочтения
Debug.Print Split(c, "_")(1)
Next c
End Sub
Пример 2: диапазон IP-адресов
Пример здесь взят из вопроса на веб-сайте StackOverflow.
У пользователя есть строка с IP-адресом в формате «BE-ABCDDD-DDS 172.16.23.3».
Он хочет, чтобы IP в диапазоне от 172,16 до 172,31 был действительным. Так например:
- «BE-ABCDDD-DDS 172.16.23.3» действителен
- «BE-ABCDDD-DDS 172.25.23.3» действителен
- «BE-ABCDDED-DDS 172.14.23.3» не действителен
- «BE-ABCDDDZZ-DDS 172.32.23.3» не действителен
Вот как бы я это сделал. Сначала я разбил строку по периодам. Число, которое мы ищем, находится между первым и вторым периодом. Поэтому это второй пункт. Когда мы разделяем строку, она помещается на первую позицию в массиве (помните, что массив начинается с нулевой позиции).
Полученный массив будет выглядеть так:
| 0 | 1 | 2 | 3 |
| BE-ABCDDD-DDS 172 | 31 | 23 | 3 |
Код ниже показывает, как это сделать.
Sub IPAdd()
' Проверьте номер, чтобы проверить разные IP-адреса
Dim s1 As String: s1 = "BE-ABCDDD-DDS 172.31.23.3"
' Разбить строку, используя символ точки
Dim num As Long
num = Split(s1, ".")(1)
' Проверьте правильность номера
Debug.Print num >= 16 And num <= 31
End Sub
Пример 3. Проверьте правильность имени файла
В этом последнем примере мы хотим проверить правильность имени файла. Есть три правила.
- Должно заканчиваться на .pdf
- Он должен содержать АА
- Он должен содержать 1234 после А
В следующих таблицах показаны некоторые допустимые и недействительные элементы:
| Имя файла | Статус |
| AA1234.pdf | Действителен |
| AA_ljgslf_1234.pdf | Действителен |
| AA1234.pdf1 | Недействительно — не заканчивается на .pdf |
| 1234 AA.pdf | Недействительно — АА не до 1234 |
| 12_AA_1234_NM.pdf | Действителен |
Сначала мы сделаем это, используя функции InStr и Right.
Sub IspInstr()
Dim f As String: f = "AA_1234_(5).pdf"
' Сначала найдите АА, так как 1234 должен идти после
Dim lPos As Long: lPos = InStr(f, "AA")
' Ищите 1234 и убедитесь, что последние четыре символа - .pdf
Debug.Print InStr(lPos, f, "1234") > 0 And Right(f, 4) = ".pdf"
End Sub
Этот код очень грязный. К счастью для нас, у VBA есть Сравнение с шаблоном. Мы можем проверить шаблон строки без необходимости искать элементы и позиции и т.д. Мы используем оператор Like в VBA для сопоставления с шаблоном. Пример ниже показывает, как это сделать.
Sub IspSravnenie()
Dim f As String: f = "AA_1234_(5).pdf"
' Определить шаблон
Dim pattern As String: pattern = "*AA*1234*.pdf"
' Проверьте каждый элемент по шаблону
Debug.Print f Like pattern ' ИСТИНА
End Sub
В приведенном выше примере звездочка в шаблоне относится к любому количеству символов.
Давайте разберем этот паттерн * AA * 1234 * .pdf
*- любая группа символов
AA — точные символы AА
*- любая группа символов
1234 — точные символы 1234
*- любая группа символов
.pdf — точные символы .pdf
Чтобы показать, что это работает правильно, давайте попробуем это на всех именах примеров в таблице.
Sub IspSravnenieTest()
' Создать коллекцию имен файлов
Dim coll As New Collection
coll.Add "AA1234.pdf"
coll.Add "AA_ljgslf_1234.pdf"
coll.Add "AA1234.pdf1"
coll.Add "1234 AA.pdf"
coll.Add "12_AA_1234_NM.pdf"
' Определить шаблон
Dim pattern As String: pattern = "*AA*1234*.pdf"
' Проверьте каждый элемент по шаблону
Dim f As Variant
For Each f In coll
Debug.Print f Like pattern
Next f
End Sub
На выходе:
ИСТИНА
ИСТИНА
ЛОЖЬ
ЛОЖЬ
ИСТИНА
Чтобы узнать больше о сопоставлении с шаблоном и ключевом слове Like, ознакомьтесь с этой публикацией.
Заключение
InStr и InStrRev действительно полезны только для простых задач, таких как проверка наличия текста в строке.
Left, Right и Mid полезны, когда положение текста всегда одинаково.
Функция Split — лучший способ извлечь переменную строку.
При попытке проверить формат строки, которая не является фиксированной по размеру, ключевое слово Like (т.е. Сопоставление с образцом) обычно обеспечивает более простое решение.
Skip to content
В руководстве объясняется, как разделить ячейки в Excel с помощью формул и стандартных инструментов. Вы узнаете, как разделить текст запятой, пробелом или любым другим разделителем, а также как разбить строки на текст и числа.
Разделение текста из одной ячейки на несколько — это задача, с которой время от времени сталкиваются все пользователи Excel. В одной из наших предыдущих статей мы обсуждали, как разделить ячейки в Excel с помощью функции «Текст по столбцам» и «Мгновенное заполнение». Сегодня мы подробно рассмотрим, как можно разделить текст по ячейкам с помощью формул.
Чтобы разбить текст в Excel, вы обычно используете функции ЛЕВСИМВ (LEFT), ПРАВСИМВ (RIGHT) или ПСТР (MID) в сочетании с НАЙТИ (FIND) или ПОИСК (SEARCH). На первый взгляд, некоторые рассмотренные ниже приёмы могут показаться сложными. Но на самом деле логика довольно проста, и следующие примеры помогут вам разобраться.
Для преобразования текста в ячейках в Excel ключевым моментом является определение положения разделителя в нем. Что может быть таким разделителем? Это запятая, точка с запятой, наклонная черта, двоеточие, тире, восклицательный знак и т.п. И, как мы далее увидим, даже целое слово.
- Как распределить ФИО по столбцам
- Как использовать разделители в тексте
- Разделяем текст по переносам строки
- Как разделить длинный текст на множество столбцов
- Как разбить «текст + число» по разным ячейкам
- Как разбить ячейку вида «число + текст»
- Разделение ячейки по маске (шаблону)
- Использование инструмента Split Text
В зависимости от вашей задачи эту проблему можно решить с помощью функции ПОИСК (без учета регистра букв) или НАЙТИ (с учетом регистра).
Как только вы определите позицию разделителя, используйте функцию ЛЕВСИМВ, ПРАВСИМВ и ПСТР, чтобы извлечь соответствующую часть содержимого.
Для лучшего понимания пошагово рассмотрим несколько примеров.
Делим текст вида ФИО по столбцам.
Если выяснение загадочных поворотов формул Excel — не ваше любимое занятие, вам может понравиться визуальный метод разделения ячеек, который демонстрируется ниже.
В столбце A нашей таблицы записаны Фамилии, имена и отчества сотрудников. Необходимо разделить их на 3 столбца.
Можно сделать это при помощи инструмента «Текст по столбцам». Об этом методе мы достаточно подробно рассказывали, когда рассматривали, как можно разделить ячейку по столбцам.
Кратко напомним:
На ленте «Данные» выбираем «Текст по столбцам» — с разделителями.
Далее в качестве разделителя выбираем пробел.
Обращаем внимание на то, как разделены наши данные в окне образца.
В следующем окне определяем формат данных. По умолчанию там будет «Общий». Он нас вполне устраивает, поэтому оставляем как есть. Выбираем левую верхнюю ячейку диапазона, в который будет помещен наш разделенный текст. Если нужно оставить в неприкосновенности исходные данные, лучше выбрать B1, к примеру.
В итоге имеем следующую картину:
При желании можно дать заголовки новым столбцам B,C,D.
А теперь давайте тот же результат получим при помощи формул.
Для многих это удобнее. В том числе и по той причине, что если в таблице появятся новые данные, которые нужно разделить, то нет необходимости повторять всю процедуру с начала, а просто нужно скопировать уже имеющиеся формулы.
Итак, чтобы выделить из нашего ФИО фамилию, будем использовать выражение
=ЛЕВСИМВ(A2; ПОИСК(» «;A2;1)-1)
В качестве разделителя мы используем пробел. Функция ПОИСК указывает нам, в какой позиции находится первый пробел. А затем именно это количество букв (за минусом 1, чтобы не извлекать сам пробел) мы «отрезаем» слева от нашего ФИО при помощи ЛЕВСИМВ.
Далее будет чуть сложнее.
Нужно извлечь второе слово, то есть имя. Чтобы вырезать кусочек из середины, используем функцию ПСТР.
=ПСТР(A2; ПОИСК(» «;A2) + 1; ПОИСК(» «;A2;ПОИСК(» «;A2)+1) — ПОИСК(» «;A2) — 1)
Как вы, наверное, знаете, функция Excel ПСТР имеет следующий синтаксис:
ПСТР (текст; начальная_позиция; количество_знаков)
Текст извлекается из ячейки A2, а два других аргумента вычисляются с использованием 4 различных функций ПОИСК:
- Начальная позиция — это позиция первого пробела плюс 1:
ПОИСК(» «;A2) + 1
- Количество знаков для извлечения: разница между положением 2- го и 1- го пробелов, минус 1:
ПОИСК(» «;A2;ПОИСК(» «;A2)+1) — ПОИСК(» «;A2) – 1
В итоге имя у нас теперь находится в C.
Осталось отчество. Для него используем выражение:
=ПРАВСИМВ(A2;ДЛСТР(A2) — ПОИСК(» «; A2; ПОИСК(» «; A2) + 1))
В этой формуле функция ДЛСТР (LEN) возвращает общую длину строки, из которой вы вычитаете позицию 2- го пробела. Получаем количество символов после 2- го пробела, и функция ПРАВСИМВ их и извлекает.
Вот результат нашей работы по разделению фамилии, имени и отчества из одной по отдельным ячейкам.
Распределение текста с разделителями на 3 столбца.
Предположим, у вас есть список одежды вида Наименование-Цвет-Размер, и вы хотите разделить его на 3 отдельных части. Здесь разделитель слов – дефис. С ним и будем работать.
- Чтобы извлечь Наименование товара (все символы до 1-го дефиса), вставьте следующее выражение в B2, а затем скопируйте его вниз по столбцу:
=ЛЕВСИМВ(A2; ПОИСК(«-«;A2;1)-1)
Здесь функция мы сначала определяем позицию первого дефиса («-«) в строке, а ЛЕВСИМВ извлекает все нужные символы начиная с этой позиции. Вы вычитаете 1 из позиции дефиса, потому что вы не хотите извлекать сам дефис.
- Чтобы извлечь цвет (это все буквы между 1-м и 2-м дефисами), запишите в C2, а затем скопируйте ниже:
=ПСТР(A2; ПОИСК(«-«;A2) + 1; ПОИСК(«-«;A2;ПОИСК(«-«;A2)+1) — ПОИСК(«-«;A2) — 1)
Логику работы ПСТР мы рассмотрели чуть выше.
- Чтобы извлечь размер (все символы после 3-го дефиса), введите следующее выражение в D2:
=ПРАВСИМВ(A2;ДЛСТР(A2) — ПОИСК(«-«; A2; ПОИСК(«-«; A2) + 1))
Аналогичным образом вы можете в Excel разделить содержимое ячейки в разные ячейки любым другим разделителем. Все, что вам нужно сделать, это заменить «-» на требуемый символ, например пробел (« »), косую черту («/»), двоеточие («:»), точку с запятой («;») и т. д.
Примечание. В приведенных выше формулах +1 и -1 соответствуют количеству знаков в разделителе. В нашем примере это дефис (то есть, 1 знак). Если ваш разделитель состоит из двух знаков, например, запятой и пробела, тогда укажите только запятую («,») в ваших выражениях и используйте +2 и -2 вместо +1 и -1.
Как разбить текст по переносам строки.
Чтобы разделить слова в ячейке по переносам строки, используйте подходы, аналогичные тем, которые были продемонстрированы в предыдущем примере. Единственное отличие состоит в том, что вам понадобится функция СИМВОЛ (CHAR) для передачи символа разрыва строки, поскольку вы не можете ввести его непосредственно в формулу с клавиатуры.
Предположим, ячейки, которые вы хотите разделить, выглядят примерно так:
Напомню, что перенести таким вот образом текст внутри ячейки можно при помощи комбинации клавиш ALT + ENTER.
Возьмите инструкции из предыдущего примера и замените дефис («-») на СИМВОЛ(10), где 10 — это код ASCII для перевода строки.
Чтобы извлечь наименование товара:
=ЛЕВСИМВ(A2; ПОИСК(СИМВОЛ(10);A2;1)-1)
Цвет:
=ПСТР(A2; ПОИСК(СИМВОЛ(10);A2) + 1; ПОИСК(СИМВОЛ(10);A2; ПОИСК(СИМВОЛ(10);A2)+1) — ПОИСК(СИМВОЛ(10);A2) — 1)
Размер:
=ПРАВСИМВ(A2;ДЛСТР(A2) — ПОИСК(СИМВОЛ(10); A2; ПОИСК(СИМВОЛ(10); A2) + 1))
Результат вы видите на скриншоте выше.
Таким же образом можно работать и с любым другим символом-разделителем. Достаточно знать его код.
Как распределить текст с разделителями на множество столбцов.
Изучив представленные выше примеры, у многих из вас, думаю, возник вопрос: «А что, если у меня не 3 слова, а больше? Если нужно разбить текст в ячейке на 5 столбцов?»
Если действовать методами, описанными выше, то формулы будут просто мега-сложными. Вероятность ошибки при их использовании очень велика. Поэтому мы применим другой метод.
Имеем список наименований одежды с различными признаками, перечисленными через дефис. Как видите, таких признаков у нас может быть от 2 до 6. Делим текст в наших ячейках на 6 столбцов так, чтобы лишние столбцы в отдельных строках просто остались пустыми.
Для первого слова (наименования одежды) используем:
=ЛЕВСИМВ(A2; ПОИСК(«-«;A2;1)-1)
Как видите, это ничем не отличается от того, что мы рассматривали ранее. Ищем позицию первого дефиса и отделяем нужное количество символов.
Для второго столбца и далее понадобится более сложное выражение:
=ЕСЛИОШИБКА(ЛЕВСИМВ(ПОДСТАВИТЬ($A2&»-«; ОБЪЕДИНИТЬ(«-«;ИСТИНА;$B2:B2)&»-«;»»;1); ПОИСК(«-«;ПОДСТАВИТЬ($A2&»-«;ОБЪЕДИНИТЬ(«-«;ИСТИНА;$B2:B2)&»-«;»»;1);1)-1);»»)
Замысел здесь состоит в том, что при помощи функции ПОДСТАВИТЬ мы удаляем из исходного содержимого наименование, которое уже ранее извлекли (то есть, «Юбка»). Вместо него подставляем пустое значение «» и в результате имеем «Синий-M-39-42-50». В нём мы снова ищем позицию первого дефиса, как это делали ранее. И при помощи ЛЕВСИМВ вновь выделяем первое слово (то есть, «Синий»).
А далее можно просто «протянуть» формулу из C2 по строке, то есть скопировать ее в остальные ячейки. В результате в D2 получим
=ЕСЛИОШИБКА(ЛЕВСИМВ(ПОДСТАВИТЬ($A2&»-«; ОБЪЕДИНИТЬ(«-«;ИСТИНА;$B2:C2)&»-«;»»;1); ПОИСК(«-«;ПОДСТАВИТЬ($A2&»-«;ОБЪЕДИНИТЬ(«-«;ИСТИНА;$B2:C2)&»-«;»»;1);1)-1);»»)
Обратите внимание, жирным шрифтом выделены произошедшие при копировании изменения. То есть, теперь из исходного текста мы удаляем все, что было уже ранее найдено и извлечено – содержимое B2 и C2. И вновь в получившейся фразе берём первое слово — до дефиса.
Если же брать больше нечего, то функция ЕСЛИОШИБКА обработает это событие и вставит в виде результата пустое значение «».
Скопируйте формулы по строкам и столбцам, на сколько это необходимо. Результат вы видите на скриншоте.
Таким способом можно разделить текст в ячейке на сколько угодно столбцов. Главное, чтобы использовались одинаковые разделители.
Как разделить ячейку вида ‘текст + число’.
Начнем с того, что не существует универсального решения, которое работало бы для всех буквенно-цифровых выражений. Выбор зависит от конкретного шаблона, по которому вы хотите разбить ячейку. Ниже вы найдете формулы для двух наиболее распространенных сценариев.
Предположим, у вас есть столбец смешанного содержания, где число всегда следует за текстом. Естественно, такая конструкция рассматривается Excel как символьная. Вы хотите поделить их так, чтобы текст и числа отображались в отдельных ячейках.
Результат может быть достигнут двумя разными способами.
Метод 1. Подсчитайте цифры и извлеките это количество символов
Самый простой способ разбить выражение, в котором число идет после текста:
Чтобы извлечь числа, вы ищите в строке все возможные числа от 0 до 9, получаете общее их количество и отсекаете такое же количество символов от конца строки.
Если мы работаем с ячейкой A2:
=ПРАВСИМВ(A2;СУММ(ДЛСТР(A2) — ДЛСТР(ПОДСТАВИТЬ(A2; {«0″;»1″;»2″;»3″;»4″;»5″;»6″;»7″;»8″;»9″};»»))))
Чтобы извлечь буквы, вы вычисляете, сколько их у нас имеется. Для этого вычитаем количество извлеченных цифр (C2) из общей длины исходной ячейки A2. После этого при помощи ЛЕВСИМВ отрезаем это количество символов от начала ячейки.
=ЛЕВСИМВ(A2;ДЛСТР(A2)-ДЛСТР(C2))
здесь A2 – исходная ячейка, а C2 — извлеченное число, как показано на скриншоте:
Метод 2: узнать позицию 1- й цифры в строке
Альтернативное решение — использовать эту формулу массива для определения позиции первой цифры:
{=МИН(ПОИСК({0;1;2;3;4;5;6;7;8;9};A2&»0123456789″))}
Как видите, мы последовательно ищем каждое число из массива {0,1,2,3,4,5,6,7,8,9}. Чтобы избежать появления ошибки если цифра не найдена, мы после содержимого ячейки A2 добавляем эти 10 цифр. Excel последовательно перебирает все символы в поисках этих десяти цифр. В итоге получаем опять же массив из 10 цифр — номеров позиций, в которых они нашлись. И из них функция МИН выбирает наименьшее число. Это и будет та позиция, с которой начинается группа чисел, которую нужно отделить от основного содержимого.
Также обратите внимание, что это формула массива и ввод её нужно заканчивать не как обычно, а комбинацией клавиш CTRL + SHIFT + ENTER.
Как только позиция первой цифры найдена, вы можете разделить буквы и числа, используя очень простые формулы ЛЕВСИМВ и ПРАВСИМВ.
Чтобы получить текст:
=ЛЕВСИМВ(A2; B2-1)
Чтобы получить числа:
=ПРАВСИМВ(A2; ДЛСТР(A2)-B2+1)
Где A2 — исходная строка, а B2 — позиция первого числа.
Чтобы избавиться от вспомогательного столбца, в котором мы вычисляли позицию первой цифры, вы можете встроить МИН в функции ЛЕВСИМВ и ПРАВСИМВ:
Для вытаскивания текста:
=ЛЕВСИМВ(A2; МИН(ПОИСК({0;1;2;3;4;5;6;7;8;9};A2&»0123456789″))-1)
Для чисел:
=ПРАВСИМВ(A2; ДЛСТР(A2)-МИН(ПОИСК({0;1;2;3;4;5;6;7;8;9};A2&»0123456789″))+1)
Этого же результата можно достичь и чуть иначе.
Сначала мы извлекаем из ячейки числа при помощи вот такого выражения:
=ПРАВСИМВ(A2;СУММ(ДЛСТР(A2) -ДЛСТР(ПОДСТАВИТЬ(A2; {«0″;»1″;»2″;»3″;»4″;»5″;»6″;»7″;»8″;»9″};»»))))
То есть, сравниваем длину нашего текста без чисел с его исходной длиной, и получаем количество цифр, которое нужно взять справа. К примеру, если текст без цифр стал короче на 2 символа, значит справа надо «отрезать» 2 символа, которые и будут нашим искомым числом.
А затем уже берём оставшееся:
=ЛЕВСИМВ(A2;ДЛСТР(A2)-ДЛСТР(C2))
Как видите, результат тот же. Можете воспользоваться любым способом.
Как разделить ячейку вида ‘число + текст’.
Если вы разделяете ячейки, в которых буквы стоят после цифр, вы можете отделять числа по следующей формуле:
=ЛЕВСИМВ(A2;СУММ(ДЛСТР(A2) — ДЛСТР(ПОДСТАВИТЬ(A2; {«0″;»1″;»2″;»3″;»4″;»5″;»6″;»7″;»8″;»9″};»»))))
Она аналогична рассмотренной в предыдущем примере, за исключением того, что вы используете функцию ЛЕВСИМВ вместо ПРАВСИМВ, чтобы получить число теперь уже из левой части выражения.
Теперь, когда у вас есть числа, отделите буквы, вычитая количество цифр из общей длины исходной строки:
=ПРАВСИМВ(A2;ДЛСТР(A2)-ДЛСТР(B2))
Где A2 — исходная строка, а B2 — искомое число, как показано на снимке экрана ниже:
Как разбить текст по ячейкам по маске (шаблону).
Эта опция очень удобна, когда вам нужно разбить список схожих строк на некоторые элементы или подстроки. Сложность состоит в том, что исходный текст должен быть разделен не при каждом появлении определенного разделителя (например, пробела), а только при некоторых определенных вхождениях. Следующий пример упрощает понимание.
Предположим, у вас есть список строк, извлеченных из некоторого файла журнала:
Вы хотите, чтобы дата и время, если таковые имеются, код ошибки и поясняющие сведения были размещены в 3 отдельных столбцах. Вы не можете использовать пробел в качестве разделителя, потому что между датой и временем также есть пробелы. Также есть пробелы в тексте пояснения, который также должен весь находиться слитно в одном столбце.
Решением является разбиение строки по следующей маске: * ERROR: * Exception: *
Здесь звездочка (*) представляет любое количество символов.
Двоеточия (:) включены в разделители, потому что мы не хотим, чтобы они появлялись в результирующих ячейках.
То есть в данном случае в качестве разделителя по столбцам выступают не отдельные символы, а целые слова.
Итак, в начале ищем позицию первого разделителя.
=ПОИСК(«ERROR:»;A2;1)
Затем аналогичным образом находим позицию, в которой начинается второй разделитель:
=ПОИСК(«Exception:»;A2;1)
Итак, для ячейки A2 шаблон выглядит следующим образом:
С 1 по 20 символ – дата и время. С 21 по 26 символ – разделитель “ERROR:”. Далее – код ошибки. С 31 по 40 символ – второй разделитель “Exception:”. Затем следует описание ошибки.
Таким образом, в первый столбец мы поместим первые 20 знаков:
=—ЛЕВСИМВ(A2;ПОИСК(«ERROR:»;A2;1)-1)
Обратите внимание, что мы взяли на 1 позицию меньше, чем начало первого разделителя. Кроме того, чтобы сразу конвертировать всё это в дату, ставим перед формулой два знака минус. Это автоматически преобразует цифры в число, а дата как раз и хранится в виде числа. Остается только установить нужный формат даты и времени стандартными средствами Excel.
Далее нужно получить код:
=ПСТР(A2;ПОИСК(«ERROR:»;A2;1)+6;ПОИСК(«Exception:»;A2;1)-(ПОИСК(«ERROR:»;A2;1)+6))
Думаю, вы понимаете, что 6 – это количество знаков в нашем слове-разделителе «ERROR:».
Ну и, наконец, выделяем из этой фразы пояснение:
=ПРАВСИМВ(A2;ДЛСТР(A2)-(ПОИСК(«Exception:»;A2;1)+10))
Аналогично добавляем 10 к найденной позиции второго разделителя «Exception:», чтобы выйти на координаты первого символа сразу после разделителя. Ведь функция говорит нам только то, где разделитель начинается, а не заканчивается.
Таким образом, ячейку мы распределили по 3 столбцам, исключив при этом слова-разделители.
Если выяснение загадочных поворотов формул Excel — не ваше любимое занятие, вам может понравиться визуальный метод разделения ячеек в Excel, который демонстрируется в следующей части этого руководства.
Как разделить ячейки в Excel с помощью функции разделения текста Split Text.
Альтернативный способ разбить столбец в Excel — использовать функцию разделения текста, включенную в надстройку Ultimate Suite for Excel. Она предоставляет следующие возможности:
- Разделить ячейку по символу-разделителю.
- Разделить ячейку по нескольким разделителям.
- Разделить ячейку по маске (шаблону).
Чтобы было понятнее, давайте более подробно рассмотрим каждый вариант по очереди.
Разделить ячейку по символу-разделителю.
Выбирайте этот вариант, если хотите разделить содержимое ячейки при каждом появлении определённого символа .
Для этого примера возьмем строки шаблона Товар-Цвет-Размер , который мы использовали в первой части этого руководства. Как вы помните, мы разделили их на 3 разных столбца, используя 3 разные формулы . А вот как добиться того же результата за 2 быстрых шага:
- Предполагая, что у вас установлен Ultimate Suite , выберите ячейки, которые нужно разделить, и щелкните значок «Разделить текст (Split Text)» на вкладке «Ablebits Data».
- Панель Разделить текст откроется в правой части окна Excel, и вы выполните следующие действия:
- Разверните группу «Разбить по символам (Split by Characters)» и выберите один из предопределенных разделителей или введите любой другой символ в поле «Пользовательский (Custom)» .
- Выберите, как именно разбивать ячейки: по столбцам или строкам.
- Нажмите кнопку «Разделить (Split)» .
Примечание. Если в ячейке может быть несколько последовательных разделителей (например, более одного символа пробела подряд), установите флажок « Считать последовательные разделители одним».
Готово! Задача, которая требовала 3 формул и 5 различных функций, теперь занимает всего пару секунд и одно нажатие кнопки.
Разделить ячейку по нескольким разделителям.
Этот параметр позволяет разделять текстовые ячейки, используя любую комбинацию символов в качестве разделителя. Технически вы разделяете строку на части, используя одну или несколько разных подстрок в качестве границ.
Например, чтобы разделить предложение на части, используя запятые и союзы, активируйте инструмент «Разбить по строкам (Split by Strings)» и введите разделители, по одному в каждой строке:
В данном случае в качестве разделителей мы используем запятую и союз “или”.
В результате исходная фраза разделяется при появлении любого разделителя:
Примечание. Союзы «или», а также «и» часто могут быть частью слова в вашей исследуемой фразе, так что не забудьте ввести пробел до и после них, чтобы предотвратить разрывы слов на части.
А вот еще один пример. Предположим, вы импортировали столбец дат из внешнего источника, и выглядит он следующим образом:
5.1.2021 12:20
9.8.2021 14:50
Этот формат не является обычным для Excel, и поэтому ни одна из функций даты не распознает здесь какие-либо элементы даты или времени. Чтобы разделить день, месяц, год, часы и минуты на отдельные ячейки, введите следующие символы в поле Spilt by strings:
- Точка (.) Для разделения дня, месяца и года
- Двоеточие (:) для разделения часов и минут
- Пробел для разграничения даты и времени
Нажмите кнопку Split, и вы сразу получите результат:
Разделить ячейки по маске (шаблону).
Эта опция очень удобна, когда вам нужно разбить список однородных строк на некоторые элементы или подстроки.
Сложность заключается в том, что исходный текст не может быть разделен при каждом появлении заданного разделителя, а только при некоторых определенных вхождениях. Следующий пример упростит понимание.
Предположим, у вас есть список строк, извлеченных из некоторого файла журнала. Чуть выше в этой статье мы разбивали этот текст по ячейкам при помощи формул. А сейчас используем специальный инструмент. И вы сами решите, какой из способов удобнее и проще.
Вы хотите, чтобы дата и время, если таковые имеются, код ошибки и пояснительная информация, были в трех отдельных столбцах. Вы не можете использовать пробел в качестве разделителя, потому что между датой и временем имеются пробелы, которые должны отображаться в одном столбце, и есть пробелы в тексте пояснения, который также должен быть расположен в отдельном столбце.
Решением является разбиение строки по следующей маске:
* ERROR:* Exception: *
Где звездочка (*) представляет любое количество символов.
Двоеточия (:) включены в разделители, потому что мы не хотим, чтобы они появлялись в результирующих ячейках.
А теперь нажмите кнопку «Разбить по маске (Split by Mask)» на панели «Split Text» , введите маску в соответствующее поле и нажмите «Split».
Результат будет примерно таким:
Примечание. При разделении строки по маске учитывается регистр. Поэтому не забудьте ввести символы в шаблоне точно так, как они отображаются в исходных данных.
Большое преимущество этого метода — гибкость. Например, если все исходные строки имеют значения даты и времени, и вы хотите, чтобы они отображались в разных столбцах, используйте эту маску:
* * ERROR:* Exception: *
Проще говоря, маска указывает надстройке разделить исходные строки на 4 части:
- Все символы перед 1-м пробелом в строке (дата)
- Символы между 1-м пробелом и словом ERROR: (время)
- Текст между ERROR: и Exception: (код ошибки)
- Все, что идет после Exception: (текст описания)
Думаю, вы согласитесь, что использование надстройки Split Text гораздо быстрее и проще, нежели использование формул.
Надеюсь, вам понравился этот быстрый и простой способ разделения строк в Excel. Если вам интересно попробовать, ознакомительная версия доступна для загрузки здесь.
Вот как вы можете разделить текст по ячейкам таблицы Excel, используя различные комбинации функций, а также специальные инструменты. Благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Читайте также:
Поиск ВПР нескольких значений по нескольким условиям — В статье показаны способы поиска (ВПР) нескольких значений в Excel на основе одного или нескольких условий и возврата нескольких результатов в столбце, строке или в отдельной ячейке. При использовании Microsoft… 
Формат времени в Excel — Вы узнаете об особенностях формата времени Excel, как записать его в часах, минутах или секундах, как перевести в число или текст, а также о том, как добавить время с помощью… 
Как сделать диаграмму Ганта — Думаю, каждый пользователь Excel знает, что такое диаграмма и как ее создать. Однако один вид графиков остается достаточно сложным для многих — это диаграмма Ганта. В этом кратком руководстве я постараюсь показать… 
Как сделать автозаполнение в Excel — В этой статье рассматривается функция автозаполнения Excel. Вы узнаете, как заполнять ряды чисел, дат и других данных, создавать и использовать настраиваемые списки в Excel. Эта статья также позволяет вам убедиться, что вы… 
Функция ИНДЕКС в Excel — 6 примеров использования — В этом руководстве вы найдете ряд примеров формул, демонстрирующих наиболее эффективное использование ИНДЕКС в Excel. Из всех функций Excel, возможности которых часто недооцениваются и используются недостаточно, ИНДЕКС определенно занимает место… 
Быстрое удаление пустых столбцов в Excel — В этом руководстве вы узнаете, как можно легко удалить пустые столбцы в Excel с помощью макроса, формулы и даже простым нажатием кнопки. Как бы банально это ни звучало, удаление пустых…