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

Combining_after

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

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.

This slideshow requires JavaScript.

STEP 1

  • Highlight the column that I want to split. 
  • Data tab – select “Text to Columns”

Slide2

STEP 2

I usually leave it as “Delimiter” as there’s always either a space or comma (“delimiter”) that the words.

Click NEXT.

STEP 3

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.

Click NEXT.

Splitting_step 3

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 choose “text”.  I can choose where (which column) I want the result to be; I’m going to leave it as it is.

Click FINISH.

Splitting_step 4

There it is – super easy!  So that I know what each column is, I added “First name” and “Last name”.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s