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 two columns into one, e.g. first and last name
There are many 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 (column D)
Excel Formula = CONCATENATE(text 1, text 2,…)
Formula Applied = CONCATENATE(A2, “ “, B2) → see orange text above
Description: Combines the names above, separated by a space (column D). This produces the same result as “Formula 1”, but uses the CONCATENATE formula.
Remember when writing functions, anything that is in text needs to have quotation marks “ “
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.
- Highlight the column that I want to split.
- Data tab – select “Text to Columns”
I usually leave it as “Delimiter” as there’s always either a space or comma (“delimiter”) that the words.
In this case space is the delimiter so that’s what I select. Leave “Treat consecutive delimiters as one” ticked. The “Data preview” shows the end result, which is what I want.
I can choose the data format that I want the result to be. In this case seeing as it’s text, I’ll choose “text”. I can choose where (which column) I want the result to be; I’m going to leave it as it is.
There it is – super easy! So that I know what each column is, I added “First name” and “Last name”.