VBA to Split Name When the Name is the Output From VLOOKUP

[DavidWF]

I use VLOOKUP to bring a person's name from a database into a form. In the database the name always has the format of christian name(s) followed by surname. There can be 1, or 2, or however many christian names.

The form requires that I enter surname in one cell and christian name(s) in a separate cell so I need to split the name.

The problem that I'm having, when I try the various methods that have previously been posted for splitting names, is that the underlying VLOOKUP formula gets split, not the visible output from the formula.

How do I resolve this so that the visible name gets split, not the underlying formula? At the moment I'm bringing the full name into cell AJ3; the surname has to be put into cell F37 and the christian name(s) into S37. If it helps, there's plenty of spare real estate to the right of the form, so if a helper cell is needed for temporary data, any cell (other than AJ3) to the right of col AH and between rows 1 to 50, is available. I'll assign the coding to a "Split Names" button on the worksheet.

 

[AlphaFrog]

[COLOR=darkblue]Sub[/COLOR] Split_Names()
    Range("F37").Value = Split(Range("AJ3").Value)(1)
    Range("S37").Value = Split(Range("AJ3").Value)(0)
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

You could do it with formulas instead...
F37
=TRIM(MID(SUBSTITUTE(AJ3," ",REPT(" ",99)),99,99))

S37
=LEFT(AJ3,SEARCH(" ",AJ3)-1)

 

[DavidWF]

Thanks AlphaFrog

I've not tried the VBA but the formula solution worked well provided there was only 1 christian name. When there were 2 christian names the formula split the two christian names and ignored the surname.

 

[DavidWF]

Just tried the VBA and it too does the same thing. I need the ability to separate the surname from the 1, or 2, or however many christian names there may be.

Thanks.

 

[Fluff]

How about
in F37
=TRIM(RIGHT(SUBSTITUTE(AJ3," ",REPT(" ",100)),100))
and in S37
=TRIM(SUBSTITUTE(AJ3,F37,""))

 

[AlphaFrog]

Names are tricky. What if you have a 2 or more word surname; Van Patten, Della Rossa, Van der Hutten?

 

[DavidWF]

Thanks Fluff.

We're halfway there - the F37 formula works perfectly.

The S37 formula returns:
For 1 christian name: F37 = christian name + surname
For 2 christian names: F37 = 2 christian names + surname
For 3 christian names: F37 = 1st and 3rd christian names + surname

Yes AlphaFrog, you're absolutely correct, names are indeed tricky and the examples you gave show this. I've scrolled back through about 5 years of the database and the occurrences of multi-name surnames are infrequent, so I'm quite happy if the formula assumes that the surname is a single word and I'll figure out a work-around for the times when the surname is multi-name. Maybe I'll need to create a Reset button that reinstates the formula in each cell if either has to be over-written because of an uncommon name structure. Just need to get the formula right first though.

 

[AlphaFrog]

Surname F37
=TRIM(RIGHT(SUBSTITUTE(AJ3," ",REPT(" ",100)),100))

S37
=LEFT(AJ3,LEN(AJ3)-LEN(F37)-1)

 

[DavidWF]

Thanks AlphaFrog, we're almost there. The S37 formula is correctly splitting out the christian name(s) but in all cases it's also adding the first letter of the surname.

 

[AlphaFrog]

Thanks AlphaFrog, we're almost there. The S37 formula is correctly splitting out the christian name(s) but in all cases it's also adding the first letter of the surname.

Change the +1 to a -1
S37
=LEFT(AJ3,LEN(AJ3)-LEN(F37)-1)