DEV Community

Cover image for SPL XLL Practice: Almighty Text Splitting in Excel
Judith-Excel-Sharing
Judith-Excel-Sharing

Posted on

SPL XLL Practice: Almighty Text Splitting in Excel

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 numeric string

The operation is very simple. Enter the formula in a blank cell of Excel:

=spl("=?.split(""\n"")",A1)
Enter fullscreen mode Exit fullscreen mode

The table with SPL code entered

? 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:

The result table

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:

The original table

Just change the content in the double quotation marks in split() to and:

=spl("=?.split(""and"")",A1)
Enter fullscreen mode Exit fullscreen mode

Returning result:

The result table

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:

The original table with mixed characters and numbers

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:

The desired table

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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.

The original table with mixed words and figures

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)
Enter fullscreen mode Exit fullscreen mode

Return words:

The result table

3. Split out date

The event memo is as follows, including date information such as 6.5.18 and 18.7.19.

The original memo table

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:

The desired result table

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)
Enter fullscreen mode Exit fullscreen mode

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.

Image description

Parse the keyword and its corresponding value, and the effect is shown in the following figure:

The desired result table

Enter formula:

=spl("=?1.property()",A1)
Enter fullscreen mode Exit fullscreen mode

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)