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:

Image 1 Excel - before separation

The text (column B) as it came into our Excel sheet from an external database

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:

Image 2 Excel - Added Columns

Temporary working columns C through H added in

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:

=(FIND(“,”,B2,1)-1

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:

Image 3 Excel - Finding the comma

Image 3 – Locating the comma’s position

 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:

 

Image 4 Excel - Isolating last name

Isolating the last name

 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:

=LEN(B2)

This means “what is the length of the character string in cell B2?”

 It looks like this:

Image 5 Excel - Finding the string length

Finding the string length

 5)  Now that we know the total character length of B2 we can isolate the first name using the following formula in cell F2:

=RIGHT(B2,(E2-C2-1))

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:

Image 6 Excel - Isolating first name

Isolating the first name

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:

Image 7 Excel - error after copying

Error after copy and paste

7)   Select the drop-down arrow on the smart tag (circled above) and select the Paste Values item (circled below)

Image 8 Excel - Paste Values

Using Paste Values to fix the error

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.

Image 8 Excel - paste values done correctly

Paste Values completed correctly

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:

Image 9 Excel - Paste Vales for first and last name

Paste Values completed for first and last name

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:

Image 10 Excel - final result

The final result

And it’s done!

If you need help with any Excel Issues you might have, let us know at enquiries@exceldimensions.com.au 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. 

 

About Paul Silverman