Excel has more than one way for splitting text, such as Text to Columns feature. However, the feature requires a manual user intervention. You can make the process dynamic with help of functions. In this guide, we're going to show you how to split text with formulas in Excel.
Before start we should warn you that you should be a Microsoft 365 subscriber to use the following formula due to the SEQUENCE function and dynamic array feature.
Data
We have rows of string containing data separated by “|” characters. Each part represents an individual column.
Syntax for splitting text with formulas
=TRIM(
MID(
SUBSTITUTE( text, separator, REPT( " ", LEN( text ))),
(SEQUENCE( 1, column count ) - 1 ) * LEN( text ) + 1,
LEN( text )))
How it works
The formula relies on replacing separator character with a long enough space-character string which will act as cushion around the string. Let’s dive into details.
The SUBSTITUTE function replaces the separator with space character.
Since any part of the whole string cannot include more characters than original string, the character count can be used to generate “long enough” padding string. This can be handled with the REPT function which simply repeats the given string a given number of times. The LEN function can provide the character count of the given text.
LEN(text)
For example, “No|Name|Type|Generation” string becomes "No Name Type Generation" after transformation.
Once replacement takes place, each data can be separated into string groups thanks to the MID function. The MID function can parse a string from a text value based on a start point and character length.
The trick behind calculating the starting point (start_num) and number of characters (num_chars) is using the length of full string. When the text with spaces is separated into chunks equal to original character length, each chunk will contain a data piece along with cushion spaces.
For example, "No Name Type Generation" string will be separated into
"No "
" Name "
" Type "
" Generation"
The calculation for calculating the star points includes the SEQUENCE function to generate numbers starting from 1 to maximum column number. The array returning from the SEQUENCE function causes the MID function return an array as well.
Finally, the TRIM function removes the cushion space characters around the separated data.
Example of splitting text with formulas
Thanks to the Excel’s dynamic array feature, you can handle multiple rows with a single data.
Bonus: LET Function version with dynamic columns
If you have the chance, check out the version with new LET function which reduces the references to deal with by defining in-formula named ranges. Although, the formula seems too complex at the first sight, it is just a version with name-reference pairs. If you want to use our splitting text with formulas approach on different places, this version will ease the process.
