ExcelTip: Text to Columns and Concatenate – two best friends

Office 365 / SharePoint Blog

If you often work with lists, especially those containing names, then you have probably dealt with having to format the data in columns.

If you have ever needed to compare lists, then you probably have needed to rearrange the content in columns.

For example, FirstName LastName, versus LastName, FirstName.

How do you handle making the switch?

Excel makes it (somewhat) easy, with the Text to Columns feature, followed by the Concatenate formula.

You can find Text to Columns under the DATA ribbon. There are a number of settings you can choose from, and I tend to use delimited, and space or comma, depending on what I need.

Once you have your data split into columns, check the rows to make sure you don’t have an extra column with information, which often happens when you have either middle initials, or a middle name without a hyphen.

To combine the columns back into one with the format you need, use concatenate.  Assuming that we start in row 2, your FirstName is in column A, and your LastName is in Column B, the following formula will combine the information as LastName, FirstName.

=CONCATENATE(B2,”, “,A2)

Note that there are commas between each section, and quotes surround the text in the middle. It might be a little difficult to see:  the quotes have a comma followed by a space between them.

What the formula is doing is concatenating, or joining, the information in cell B2, then adding a comma followed by a space, and then adding the information in cell A2.

To learn more about these functions, see

Advertisements

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