|

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.

Loader Loading…
EAD Logo Taking too long?

Reload Reload document
| Open Open in new tab

Download [10.21 MB]

Similar Posts

More Macro Practice - Part IV

Okay, Yet More Macro Practice

I didn’t think I would, but I actually worked a little bit more on macro programming for that cryptography game/puzzle. This time I created a macro that would generate a list of 2  and 3 letter words, including nonsensical words. The 2 letter list doesn’t take to long to build but the 3 letter one…

Working on Data and Storytelling
|

Working on Data and Storytelling

When you read about data science or data analytics, you read a lot of exhortations that analysis alone is not enough: you have to be able to tell a story with the data if you want to get action or receive respect from your coworkers. That’s easier said than done. It’s one thing to tell…

GRRR : Microsoft protection and the zone identifier - problems with downloaded files from the internet

Microsoft’s Zone Identifier

Grrrrrrrr. There has been so many obstacles lately where things just don’t work. It’s been discouraging. This time it is a Windows machine that won’t allow my macro to recognize files downloaded from the internet which is where our vendor site lies. Let’s call the person with a machine that won’t see the downloaded files…

163 vs 32

Special characters: 160 versus 32

I ran across a new variation on hidden characters. In the past, I have written about invisible characters such as an “insert line” in this post. I wrote about how to use the function “code” to get at the hidden character and how to use function “char” to create that hidden character. I also may have…

Macros to copy or move files from one folder to another

Creating macros to move or copy files

Creating macros to move or copy files from one folder to another should be easy but it turned out to be problematic.I’m working on a new project called the Paperless Office project (I named it that). I’ve been doing a lot of research and some of that research is trying to see if I could…

Researching Power Automate
|

Researching Power Automate

I was doing a bit of research today to see if I could utilize Microsoft’s Power Automate (or Power Flow?), but alas, no, it does not appear that I can – at least not in the fashion that I would like. Here’s a couple of things I learned: Unfortunately, the only Excel Power Automate will…