Text splitting is a common problem encountered in work. For common splitting with commas, semicolons, spaces, and other delimiters or regular fixed-width splitting, Excel’s “text to columns” function can be directly used for splitting. However, more complex text splitting in Excel is often cumbersome or difficult to implement.
It is easy to handle with SPL XLL, which has a specialized text-splitting function that can be split by any delimiter. It can also extract numbers, and dates, parse K-V formats, etc. from strings, making it very useful.
1. Split by separator
If it is a text separated by a single character such as a comma or a semicolon, Excel’s “text to columns” function can be used to implement it.
If it is text separated by carriage returns or multiple characters, splitting text into columns is not easily implemented directly. Using SPL’s split() function to split strings, simply write the separator character directly into the parameters, which supports both single and multiple characters.
(1) Separate by a carriage return (Enter)
Split a numeric string separated by a carriage return into multiple columns.
The operation is very simple. Enter the formula in a blank cell of Excel:
=spl("=?.split(""\n"")",A1)
? indicates the content to be split, which refers to A1 here. split() is a string splitting function, and ”\n” represents a carriage return. Note that quotation marks in Excel strings should be represented by double quotation marks. The meaning of the whole formula is to split the string in cell A1 according to the carriage return character.
Splitting Result:
Similarly, if you split with other separators, you only need to change the content within the double quotation marks, such as separating with multiple characters.
(2) Separate by multi-characters
Split the following string with “and” to find the name of each fruit:
Just change the content in the double quotation marks in split() to and:
=spl("=?.split(""and"")",A1)
Returning result:
2. Split out numbers and words
（1）Split out numbers
There is a column of data that mixes characters and numbers, where numbers and characters may appear in multiple places with varying patterns of occurrence:
It is necessary to separate the numbers and characters in each row, placing the separated numbers in column B and the remaining characters in column C. The effect is shown in the following figure:
Excel has a “flash fill” function, but it requires manual setting of the first line reference value, which is cumbersome and prone to errors. Moreover, the flash fill function cannot separate some characters, and it is likely that VBA needs to be written to achieve the result in the figure.
SPL XLL is much more flexible, writing in B1:
=spl("=?.words@d().concat()",A1)
words()means to separate English letters and other characters in a string, words@d() represents returning numbers, such as 1, 3, and 6 in the first row, and concat() represents a concatenation, such as 1, 3, and 6 concatenating to form 136.
Drag and copy B1 to each row to obtain the numerical column B in the figure above.
Write in C1:
=spl("=(?.words@w()\?.words@d()).concat()",A1)
words@w() represents returning all disassembled characters, and the symbol \ represents the difference set. Subtracting the numbers from all characters is the remaining string, which is also column C.
Drag and copy C1 to each row to obtain the character column C in the figure above.
Changing the letter option after @ of the word() function can return different separated terms.
（2）Split out words
The words() function is often used to split words in a string, such as the following figure, and to extract words such as apple and banana.
Similarly, this irregular text splitting in Excel is not easy to implement. With SPL XLL’s words() function, it’s very easy.
Enter formula:
=spl("=?.words()",A1)
Return words:
3. Split out date
The event memo is as follows, including date information such as 6.5.18 and 18.7.19.
Separate the dates in the information and separate them with semicolons before placing them in the following column for future statistics.
The effect is as follows:
This type of date separation has an indefinite number of dates in one sentence, and can usually only write VBA or use regular expressions to match and parse, which is very cumbersome. And using SPL XLL is much simpler.
Write the formula in B1:
=spl("=?1.split("""").(date(~,""dd.MM.yy"")).select(ifdate(~)).concat("";"")",A1)
Split the string with spaces to convert the split string into date-type data in the specified format.
Select date-type data and concatenate it into a string using semicolons.
Drag and copy to each row to obtain the result in column B of the figure above.
4. Parsing Key-Value Pair
There are the following strings, with multiple keyword keys, each with a corresponding value.
Parse the keyword and its corresponding value, and the effect is shown in the following figure:
Enter formula:
=spl("=?1.property()",A1)
Return the result in the figure.
The property() function directly parses the desired table, which is simple and convenient.
SPL XLL functions can implement various forms of text splitting, making the operation simple intuitive, and easy to understand.
The functions of SPL XLL go far beyond that. It also has many flexible data processing functions that can handle various complex Excel operations, and the syntax is very simple, making it very user-friendly.
SPL XLL download address: esProc Desktop Download
Plugin Installation Method: SPL XLL Installation and Configuration
Reference cases: Desktop and Excel Data Processing Cases
Top comments (0)