Excel Formula Development in the Real World
~~by Ray Blake, GR Business Process Solutions

Name field reconcatenation in Excel explained through building worksheet formulae and custom VBA functions.  

There must have been times for all of us when we’ve been presented with a large database containing people’s names and other data which needs to be manipulated or reported on in some way, probably in alphabetical order. As the records appear in Excel for the first time, the heart sinks as the first column contains the names: 

John Smith
Kevin Adams
etc.

I think that 9 out of 10 such lists I am given contain a name field formatted in this way, first name first. On the other hand, 9 out of 10 clients want their analyses presented in alphabetical order by surname. If you often find yourself in the same boat, this article will help you. We shall start by constructing an Excel worksheet formula to reverse the first name and surname positions in the field. Then we’ll work to expand the function so that it can deal easily with initials, multiple forenames or any combination of these features. Finally, we’ll consider the potential disadvantages of such a function, and look at an alternative approach, using VBA to construct a custom worksheet formula and overcome those disadvantages. 

The basic task is to take the existing entry and re-engineer it, thus: 

‘John Smith’ becomes ‘Smith, John’

At first glance, this is an easy problem. All we need to do is look for a space in the text, carve up the string and then recombine it appropriately. If A1 contains the original string, then A2 can contain the formula: 

=SEARCH(" ",A1)

This will return a number representing the first space character within the string in cell A1. In our example, it will give ‘5’ because the fifth character in the string is the first space. 

Using this, we can chop up the string into its two parts. In A3, enter: 

=LEFT(A1,A2-1)

This formula tells Excel to return the leftmost 4 characters from the string in A1. It’s 4 because that is the result of 5 minus 1, so the formula returns all characters to the left of the space, but not the space itself.

In A4, we need: 

=RIGHT(A1,LEN(A1)-A2)

There are two functions here. Firstly, LEN simply returns the length of a string. RIGHT works the same way as LEFT, only… well, you know. So this formula returns just the characters to the right of the space in the string in A1. 

Let’s summarise what we have so far: 

Now, we can put our final version in A5 as follows: 

=A4 & ", " & A3

This will return in our example: Smith, John. Our work is done. 

One word of caution here. If the person entering the data has put extra spaces in at the start or end of the name string, your functions will have a hard time. If you suspect this may be the case, you can replace every reference to A1 with: TRIM(A1). This will have the effect of removing all leading and trailing spaces from the string. 

Back to our function.  Type into cell A1 the name John James Smith and see what happens. 

Things start going wrong in A3, where only the first forename appears, and we end up with ‘James Smith, John’. Oops. As it stands, our formula will not cope properly with more than one space in the string.

Here are some more entries which would cause issues: 

• J J Smith

• J. J. Smith

• John J Smith

• John J. Smith

• John James William Albatross Smith

Remember the SEARCH function in A2? That looks for the first space character in the string. We can get it to look not for the first instance of a space, but for the second, by simply adding a third argument to a new formula in A6, thus: 

=SEARCH(" ",A1,A2+1)

The effect of this third argument is to search again for a space, but starting to search one character to the right of the first space we found.  

If there isn’t a second space, the formula will return an error, but we can allow for that like this: 

=IF(ISERROR(SEARCH(" ",A1,A2+1)),A2,SEARCH(" ",A1,A2+1))

This looks for the second space. If this would cause an error, then it just returns the position of the first, as found by the formula in A2, but if looking for the second doesn’t generate an error, then that’s what the formula does. This approach won’t always work, though, because there may be more even than two spaces, as in the last example above. 

Ideally, we could get the SEARCH command to start at the end of the string and work backwards, but it doesn’t work that way and there is no worksheet formula which does. 

Instead, we have to find an approach which will find out how many spaces there are in the string and then give the position of the last one. Actually, we can achieve this quite easily using just the formulas we’ve already met here, plus one other. 

In cell C1, enter:  

John James William Smith

In C2 enter the new formula: 

=SUBSTITUTE(C1," ","")

The first set of quotes in this formula contains a space, the second nothing. The effect of this formula is to return a version of the string without any spaces, thus: 

JohnJamesWilliamSmith

In C3, you can enter this formula: 

=LEN(C1)-LEN(C2)

This tells you the difference between the number of characters in John James William Smith and the number of characters in JohnJamesWilliamSmith. The result is 3, which is, of course, the number of spaces in the original string. 

But the SUBSTITUTE has an optional fourth argument as well, and it is this one which we will now put into use.

In C4, enter: 

=SUBSTITUTE(C1," ","^",C3)

That fourth argument is the instance number you want to substitute. If you omit it, this formula will replace every space with a caret (^). But by giving the instance number as the number of spaces we calculated in C3, it will only change the last space to a caret, returning this string: 

John James William^Smith

Now, unless your data has a lot of caret characters in it (and if it does, use a different symbol) you can be pretty sure that there will only be one in the string, so it is this character which now becomes the object of your new SEARCH command in cell C5, thus: 

=SEARCH("^",C4)

In this case, the formula produces 19, because the caret is the 19^th character in the string. From there, it is an easy job to play with the string using the LEFT and RIGHT formulae as we did before. Below is a summary of the full process thus generated: 

Now, this is a lengthy process, but once you’ve been through it, you can turn this chain of formulae into one big, Swiss Army Knife of a formula which looks like this: 

=RIGHT(C1,LEN(C1)-SEARCH("^",SUBSTITUTE(C1," ","^",LEN(C1)-LEN(SUBSTITUTE(C1," ",""))))) & ", " & LEFT(C1,SEARCH("^", SUBSTITUTE(C1," ","^",LEN(C1)-LEN(SUBSTITUTE(C1," ",""))))-1)

If you’ve been using the TRIM function as suggested above, you have an even longer formula: