Combining & Splitting Text
Knowing how to combine two columns into one and vice versa quickly is so helpful. I remember having to do this so many times and I thought surely there is a quicker way!
Combining
Combining two columns into one, e.g. first and last name
There are three ways of doing this:
Formula 1: =A2&” “&B2 Description: Combines the names above, separated by a space [“ “] (column C)
Formula 2: =B3&”, ”&A3 Description: Combines the names above, separated by a comma [", "] (column D)
Formula 3: =CONCATENATE(A2, " ",B2)
Description: Combines the names above, separated by a comma (column D). Notice the result is the same as in “Formula 1”, but uses the CONCATENATE formula.
Splitting
Splitting one column into two, e.g. first and last names are in one column and you want a separate column for first name and another for last name.
STEP 1
Highlight the column that I want to split. Click the DATA tab > Under "Data Tools" select “Text to Columns”.
STEP 2
I usually leave it as “Delimited” as there’s always either a space or comma (“delimiter”) that separates the words.
Click NEXT.
STEP 3
In the "Delimiters" box, I'm going to select "Comma" as in this case comma (Before (1)) is my delimiter. If I was going to separate the column, Before (2), I'd select "Space". I'll leave “Treat consecutive delimiters as one” ticked. The “Data preview” shows the end result, which is what I want.
Click NEXT.
STEP 4
I can choose the data format that I want the result to be. In this case seeing as it’s text, I’ll leave it as "General”. I can choose where (which column) I want the result to be which I'll change to $B$1 (After (1)).
Click FINISH.
There it is – super easy! So that I know what each column is, I added “First name” and “Last name” as a column heading.
Comments