It’s a bad day in spreadsheet land when you have to split your data into columns before you start analyzing. But Excel’s got a new function called TEXTSPLIT that makes splitting text into multiple columns simple and can be used to automate Excel’s “Text to Columns” data tool.
To use the TEXTSPLIT function, select the cell containing the text you want to split as the text argument and enter the character that defines where to split the text as the col_delimiter. Take a look at my example above. I have the first, middle, and last name in one cell, and I want to split the full name into three separate columns. I selected the cell with the full name as the text argument and entered a space (“ ”) as the col_delimiter because I want to split the text in every space. You can also input multiple delimiters separated by a comma as long as you enter them in curly brackets (ex. {“ ”,“-”, “.”}).
The TEXTSPLIT function also has four other optional arguments that might be beneficial when splitting text. The first is row_delimiter, an argument used if you want to separate text into rows instead of columns. The second is ignore_empty, which tells the function whether or not to ignore empty cells. The third is match_mode, which can set the search for a case-sensitive delimiter. And lastly, pad_with determines the value used to pad missing values in 2D arrays.
