Code to extract surname from cell

[Adam72]

I have an excel file which contains the full name of clients which has already been concatenated, and need to extract the surname back out of the string to be able to sort alphabetically on surname. The names are in the following format:

Givenname Givenname SURNAME

To allow for instances where the surname may have more than 1 than one name eg. Givenname MY SUR NAME I need to develop a code to FIND the first instance of double capitals in the string, input this as the start point for a MID command to extract the surname and then paste it into another cell on the same line, then move onto the next row.

Appreciate any help!!

 

[HarleyQuinn]

Hi Adam,

This thread may be better posted in forum68

Regards

Andy
---------------------------------
[green]' Signature removed for testing purposes.[/green]

http://lessthandot.com

 

[SidYuca]

Seems like an interesting problem and OK for this forum if we treat it as a file of names instead of cells in a spread sheet. Looks like you can run into a few problems anyway you attack it. If you think of names like:
O'REILY
Van Slyke
McDuck

I imagine the last two could be all caps but the first may be a problem.

How about searching for the location of blanks in the string then starting at the first blank add the ascii of the next two characters and if the sum is <180 you have two caps. If the space was at position S then the lastname = mid("fullString", S+1, len("full string")-S)

 

[HarleyQuinn]

Sid,

This question has been answered in several different ways in Adam's other thread in the Office forum thread68-1581748

Regards

Andy
---------------------------------
[green]' Signature removed for testing purposes.[/green]

http://lessthandot.com

 

[TysonLPrice]

Google "Excel extract surname". I saw potential there. Once you key in the formaulas and get them working do it again while recording a macro. Then open up the macro in the Visual Basic editor and check out the code it generated.

 

[Swi]

Try this.

http://www.pscode.com/vb/scripts/ShowCode.asp?txtCodeId=36765&lngWId=1

Swi