Excel Trick - Splitting Data into Multiple Columns

Excel to me can be the greatest program…or the worst, depending. I am always finding new tricks and formulas to get the cells to do what I want.

Recently I had a file of over 3,000 names. I wanted to add them to our system, however, the file had the first and last name in one cell together and I needed them in separate cells.

I have found a formula for this in the past, which works, but I forget it more than I remember it. So many excel formulas running around on bits of post-it around my desk…rather crazy.

So I found this after messing around for a while and getting sick of it.

So the steps are quick and easy:

1) You want to insert a few extra columns (Since I was dealing with names I added 3 or 4 because some people hyphenate their last name or included their middle or just had 2)

2) Hightlight the column you want to separate (for me it was the column with the full name)

3) In the top tool bar select Data

4) Click on Text to Columns option

5) Choose Delimited

6) Select Next

7) Select Space  and Treat consecutive Delimiters as one– no other option needs to be checked

8) Select Next

9) Select General (may already be preselected)

10) Select Finish

Now that was quick and easy and saved me so much time with 3,000 + names.

I emailed my best friend and told her about this, she said…damn I could use that. Don’t forget! It got me to thinking, maybe it isn’t just us who could use this. SO..I tossed it into a blog…so that me and my best friend can reference it in the future and other people can find it and hopefully get some help from it