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”.

Advertisements

One thought on “Combining & Splitting Text

  1. Wow, fantastic weblog layout! How lengthy have you ever been blogging for? you make blogging glance easy. The overall glance of your web site is wonderful, as smartly as the content!

    Like

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