.png)
You know that feeling when you have a perfectly good spreadsheet, and then you realize you need a seemingly simple change that will affect EVERY record? Maybe you should have had first and last name in the same cell, or maybe you should have broken them apart. Maybe you needed it to be "Last Name, First Name", but you made it "First Name Last Name." Don't worry! It's ridiculously easy, and will make you look magical to the non-Excel user.
For this installment, we're going to talk about the Concatenate and Text to Columns features. They're simple, fast, and so useful for large data sets.
Concatenate - the definition is "to link things together in a chain or series." So let's chain some things together! The traditional for the Concatenate formula is:
=CONCATENATE(A1, B1)
The hardest part of this formula has always been remembering how to spell "concatenate." However, in newer versions of Excel, they've simplified it - just use the ampersand! So now the formula can also be:
=A1&B1
How easy is that?? So let's play around with the name Jane Smith:
In the first sample, notice that there is no space between Jane and Smith. So the answer is NOT to simply enter =A1&B1. Excel only does exactly what we tell it, and we told it to put Jane and Smith together. In the second formula we use =A1&" "&B1. See the quotes? Anything you put in quotes like that will display exactly as you type it. There's a single space between those quotes, so now we have Jane Smith. That also means if you put =A1&" Awesomesauce "&B1 it would return Jane Awesomesauce Smith, as you can see in the third result.
Now let's do the opposite! We'll use Text to Columns to break the information apart. This is a little different - instead of using a formula, we'll use a function. While we're at it, let's also explore why it's so important to have consistent data.
First, highlight the column you want to break apart. Then go to the Data tab and select Text to Columns. It will initiate a wizard popup. Make sure delimited is selected. You can also see a preview of the data you selected you selected. If it all looks correct, click Next.
Make sure only 'Space' is selected, and see the preview at the bottom. By selecting 'Space,' we're telling the wizard to break the data apart at each space. When this happens, the space is removed, and the data directly after the space is moved one column to the right. If there is more than one space in a cell, there will be a break at each space and the data following each space is moved over to the next column.
Here's the result. You can see that the first five names divided as expected, with the first name in Column A and the last name in Column B. However, since the sixth name had two spaces - one on each side of the middle initial - it was split twice:
With the Text to Columns function, all you have to do is click 'Undo' (CTRL+Z) to switch your data back to its original format. So if you try this and it doesn't work right on the first try, just click "Undo" and try again!
We discussed in the previous post how important it is to clean your data and this is a great example of why that is so important. As always, there is a quick fix for when you see this kind of inconsistency. With your data back in its original state, turn on your filters (click row one and press CTRL+SHIFT+L) and filter to ". " ( a period followed by a space) and click 'OK'. This will filter down to all of the entries with a middle initial. If there are only a couple of entries, it's easy enough to manually remove those initials and make them match the other First Name Last Name-style entries. But what if you have a few dozen?
It's still easy! In this example, you would just highlight your data's column (in its original form), search (CTRL+F) for " * " (space asterisk space), click Replace, and enter a single space in the Replace field. Then click Replace all. You're telling Excel "find everything that has a space and then data and then another space, and replace it all with one space." That means Jane A. Smith would become Jane Smith, and so would Jane Awesomesauce Smith. It will only remove data with a space on either side, and it will leave one space behind.
A few other things to look for if you see inconsistent data in names - any periods or commas. Commas could be in names with a suffix, like "John Smith, Jr." and could also be in names listed in the reverse order, like "Smith, John." Periods could follow middle initials (Jane A. Smith) prefixes (Ms. Jane Smith) or Suffixes (John Smith, Jr. again!). Depending on your data, you may be able to remove the prefix and leave the suffix with the last name, or move it to a specific suffix column. 'Last Name, First Name" is a little trickier to fix when it's mixed in with "First Name Last Name", but if you filter to entries with a comma, you can then manipulate the whole group at once. Give it a shot! Play around, undo when necessary, and see how it works for you. Leave a comment and tell me about your results!
Comments