Andrew Dove

# Text to Columns formula

Updated: Jan 16, 2019

If you find that Text to Columns is slowing you down, there is a formula that you can use instead. Most of the formula variations found can only delimit for a space. However, a very simple modification can enable the use of a wide range of characters including alphanumeric.

In the following formula, the main difference is the last section, CHAR(CODE($B4)) which adds flexibility.

=SUBSTITUTE(MID(SUBSTITUTE($A4,$B4,REPT($B4,LEN($A4))),(COLUMN()-3)*LEN($A4)+1,LEN($A4)),CHAR(CODE($B4)),"")

Text to parse examples:-

"aZbZc" with delimiter of "Z" gives "a" "b" "cC in first 3 cells

"1%2%3" with delimiter of "%" gives "1" "2" "3" in first 3 cells

"input as a" gives "input" "as" "a" in first 3 cells

Please note that while the function COLUMN () allows this formula to be copied across rows without editing, you will need to adjust "-3" to the correct value depending upon which row the formula is used in first.