Excel Trick - Combining Data From Two Columns Into One

My fun tricks with Excel just keep coming. Recently I posted a blog about how to split data into multiple columns. That is great and can come in handy, but sometimes, you need those first and last names in the same column. It is easy to just type them if there are 5-10 but when you get into the hundreds and thousands…not so much.

Let’s say A1 contains “Lady” and B2 contains “Dame”

Insert a new column in after B and in the new C column type

=A1&” “&B1

Boom

C1 now says “Lady Dame”

Hopefully this not only saves me next time I am trying to remember this fun trick, but for those of you who find it can from now on remember that combining two columns in excel is not as hard or time consuming as some think!

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