Changing family name-maiden name

[Lucien]

Hi All,
I have two separate sheets with names which I want to link using V-lookup using False as criteria (exact finding). However in one sheet voor women the maiden name is in front.
For example:
- sheet one: Jansen-Bush
- sheet two: Bush-Jansen

I want also in sheet two the notation Jansen-Bush.

I can only solve this problem with a very large formula. Has some of you a solution??

Thanks!

 

[phantom1975]

WELCOME TO THE BOARD!

This will convert it for you:

=RIGHT(A1,LEN(A1)-SEARCH("-",A1,1))&"-"&LEFT(A1,SEARCH("-",A1,1)-1)

You can either convert it and use the results in your VLOOKUP, or put that formula in the lookup portion of your VLOOKUP.

Does this help?

 

[Haluk]

Hi;

Not a professional alternative as phantom's answer but, this the one i've thought; ( you may need to change ";" with "," )

=MID(A4;FIND("-";A4;1)+1;LEN(A4)) & "-" & MID(A4;1;LEN(A4)-LEN(MID(A4;FIND("-";A4;1);9999)))

Edit: The formula works for a string in cell A4
This message was edited by Raider on 2002-11-10 13:40

 

[Lucien]

Thanks to both of you, it works!!!!

 

[Aladin Akyurek]

I can't resist to throw in Longre's WMID...

=WMID(A1,2,1,"-")&"-"&WMID(A1,1,1,"-")

which is part of the fast morfunc.xll add-in, downloadable from:

http://longre.free.fr/english/index.html