• 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.


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.

13 views0 comments

Recent Posts

See All

01383 882837

©2018 by Magnify Excel. Proudly created with