We were working with a client in Melbourne recently, and a question came up (yet again) about how to manage some text that had been imported into Excel from an external database.
This is becoming more common, as the number of people increases who use Excel to manipulate data originating from outside databases.
Why’s it necessary? It’s because database designers might be primarily concerned with capturing data, rather than thinking ahead to what the user will need to use the data for.
Have a look at the example below, in column B:
As you can see, the data appears in Last Name, First Name format, with both names being separated by a comma. If we use this for a mail merge in Word, for example, it’s going to be difficult to customise our field so our mailout is addressed to either Dear John, or Dear Mr. Symons.
Here’s how we can fix it.
1) Insert 6 blank columns. I’ve added headers to show you what’ll go where. You’ll remove 4 of these plus one other when were finished. It’ll look like the example below:
2) In Column C we locate the position of the comma that separate the first and last name. The formula we’ll put in cell C2 is:
This means: “find the position number of the comma in cell B2 starting at character position 1, then subtract 1 from that number to ignore the position number of the comma.”
This is the result:
3) In Column D we can now isolate the last name using the following formula:
=LEFT(B2,C2) which we’ll enter into cell D2
This means “from the character string in cell B2, show the 5 characters starting from the left hand side”.
This is the result:
4) The next thing we need to do, in order to isolate the first name, is to determine how long the character string is.
In Column E we use the following formula in cell E2:
This means “what is the length of the character string in cell B2?”
It looks like this:
5) Now that we know the total character length of B2 we can isolate the first name using the following formula in cell F2:
This means “From the right hand side of cell B2 show the 5 characters, calculated by subtracting the comma position from the total length of the character string, then removing 1 more character.
It looks like this:
At this point we’ve isolated the first name and the second name. We’ll now copy the contents of copy Column F to Column G and Column D to Column H.
We’ll use Paste Values here, because we don’t want to copy the formulas, just the resulting values.
6) Select the range F2:F7. Press Control-C to copy the content, click into cell G2, then press Control-V to paste.
At first, G2:G7 will display #VALUE! error because it has copied the formula and this is generating an error, as shown below:
7) Select the drop-down arrow on the smart tag (circled above) and select the Paste Values item (circled below)
The first names will now appear in column G. While they look the same as the contents of F2:F5, they’re different as F2:F5 contain formulas whereas G2:G5 contains only text.
8) Repeat this process, copying the list of last names from cells D2:D7 to H2:H7, remembering to Paste Values
This is what you’ll have:
9) At this point, C through F, which were really only temporary work columns, aren’t needed any more. Also, Column B won’t be needed either. Delete columns B through
The result is this:
And it’s done!
If you need help with any Excel Issues you might have, let us know at firstname.lastname@example.org or call us on 1300 730 922.
Can we help with your Excel on-site training requirements? Call us for information and pricing. We have a large client base Australia wide, and we’d be pleased to show you how we can increase your team’s Excel productivity.
Note: we try and break down our examples into easy-to-follow steps. This may not be the most efficient way to solve the problem, but we think it’s easier to understand. And once you understand the parts of the solution, you can then put the solution together in a way that’ll work for you.