You’ve probably had to convert text to columns before in Excel. Usually in one cell, you’ll have a long line of text that is separated by commas, semicolons, or some other delimiter and all you’re trying to do is get each value into its own column. Something along the lines of this:
This is probably one of the most common use cases of Text-to-Columns I’ve seen. You have text in the format “Last Name, First Name” and you want to split this into two columns with one column being the First Name and the next column being the Last Name. What happens when you have multiple text in a cell entered in as new lines like this?
This is all in one cell and each text is separated by a new line. In this case, it looks like this was a database dump and all the text is put into one cell and our job is to put each value into a new column. The problem is, there is no delimiter! You could add a comma after each value but that would take forever if you had a cell with say 50 lines of text. How do we solve this?
Convert Text to Columns For Cell With Multiple Lines With SUBSTITUTE()
The simple answer is using the function SUBSTITUTE() . It doesn’t seem intuitive, but what we need to do is format the cell with multiple lines of text so that it’s easy for the Text-to-Columns operation to work. We basically want the text in this cell to look like this:
Notice the commas after every value? Once the text in the cell looks like this, then we are ready to use the Text-to-Columns button to split the text up by the commas that separates each value. The key with using the SUBSTITUTE() function is we want to replace each new line with a comma. The ASCII character code for a new line break is 10 for PCs and 13 for Macs. In Excel, you can use the CHAR() function to represent different ASCII codes so we can do CHAR(10) to represent a line break. So in a a cell next to the cell with all your text, you can write the following formula to replace all the line breaks with a comma:
Let’s discuss what this formula does. All the SUBSTITUTE() function does is replace a character or characters in a text with another character. In this formula, the cell with the data in multiple lines is B2. The second value in the SUBSTITUTE() function is the actual text we are trying to find in the cell. In this case, the “text” we are trying to find is a new line break which is represented by CHAR(10) which we just discussed. Finally, we want to replace all occurrences of the new line break with a comma which is the last value in the SUBSTITUTE() function. If you apply this formula to this text, the result will look like this:
You’ll notice all our text goes onto one line with each value separated by a comma. This is exactly what we want because now we can use the Text-to-Columns operation to split this long text into columns.
Copying Text As Values For Text To Columns
If you try to use the Text-to-Columns operation on the cell where you have the SUBSTITUTE() function you’ll notice you’ll get this in the dialog box:
This isn’t correct because we don’t want to do text to columns on the text of the SUBSTITUTE() function, but rather on the resulting text of the function. All you would need to do at this point is to a Copy and Paste Special Values so that we get just the values from the function rather then the formula. After you do the Copy and Paste Special as Values, make sure you selected “Delimited” in Step 1 of the Text-to-Columns dialog box. Then you want to select Commas in Step 2:
You’ll see a preview of what the data will look like and the result looks exactly like what we want: the text (separated by commas) is split into multiple columns. The result in Excel should look like this:
There are multiple ways of solving problems in Excel, and this example shows how you can use different text hacks to get the result you want. This exercise was actually something asked of me in a workshop I gave a few weeks ago, and I didn’t know the answer until after I found that new line breaks are represented by CHAR(10) in Excel. Once I figured this out, I knew using the SUBSTITUTE() function, Paste Special as Values, and the Text-to-Columns would solve the problem of getting the source text into new columns.