4 Ways for Excel to Split a Column
A couple of weeks ago I was working with someone and she asked me to show her a way of splitting a column. The route she took wasn’t working. During the attempt to show her, I learned a new process. A very easy process. No formulas required. No tricks.
Example
Let’s use an easy example.
The image to the left is a list of books and their made-up prices, all under column D.
My associate was working with expense general ledgers with costs listed. In the spirit of keeping confidential data confidential, I’m using a made-up example.
The data my associate was using came from something she had copied, and the pasting ended up with GL and expenses in one column.
What we needed was that single column split into two. In this example, I need a column with a list of books and another column with the associated prices.
There are actually 4 ways of accomplishing this task, three of which I could think of right off the top of my head.
Option 1 – TEXT to COLUMN
The first option is the TEXT to COLUMNs feature found under the Data section in the menu.
We played around with that particular feature, but we couldn’t separate the text from the numbers. We could only split by the spaces which yielded an unsatisfactory result.
Option 2 – Power Query
Next is a really nice feature but you have to get comfortable with it and we were conversing by Zoom, so the connection was kind of slow. It would have just been too much of a hassle to explain Power Query to her.
Option 3 – Formulas
Formulas could work but who knows how long it would take me to figure out a formula?
We ended up asking CoPilot if it could come up with a method of splitting a column of text and numbers into two nice columns: one for text and one for numbers.
And it did.
By the way, the image to the right provides you with the formulas to use.
To get at the text, column E had the formula:
= LEFT(D1, MIN(FIND({0,1,2,3,4,5,6,7,8,9},D1 & “0123456789”)) – 1)
To get at the numbers, column F contained:
=MID(FIND({0,1,2,3,4,5,6,7,8,9}, D1 & “0123456789”)), LEN(D1)))
Option 4 – Flash fill
Flash fill is where you just go into a new column and just start typing in what you want to show up under that column, in this case, the first book. You just keep typing in the name of the books until Excel fills it in for you (usually by the 2nd or 3rd row).
Then you go to the next column and do the same for the numbers.
In the image on the left, I’m typing in “Disrupting Finance” when Excel recognized the pattern and just filled in the rest of the list of books.
Yes, AI really comes in handy when it comes to coming up with good solutions that may take one forever to dream up.
I’m going to include the entire PDF presentation, but it may not work due to the constant updates. You might have to hit reload.
Loading…
Reload document 
Open in new tab 
You must be logged in to post a comment.