Extract given and surname a string
On Sun, 29 Apr 2007 11:33:09 +1000, "Bob Maloney" wrote:
Hi,
Excel 2003.
I have the following example text in Sheet1 cell A2 (it is pasted from
another application) :
AgentSmith Mary (12178)
It is made up of:
"Agent" - Constant text
"Smith" - Surname (Variable text and length)
"Mary" - Given name (Variable text and length)
"(12178)" - Identifying number (Actual number and length will vary. Brackets
will always be constant).
I would like to extract both the given and surname (proper case) and place
the result in Sheet2 cell B19, like so:
Mary SMITH
Any assistance would be appreciated.
Cheers
Bob
Given your fairly precise format, the following should work:
=PROPER(MID(Sheet1!A2,FIND(" ",
Sheet1!A2)+1,FIND("(",Sheet1!A2)-FIND(" ",
Sheet1!A2)-1))&UPPER(MID(Sheet1!A2,6,
FIND(" ",Sheet1!A2)-6))
I'm not sure, from your description, if the PROPER function is necessary. If
not, then:
=MID(Sheet1!A2,FIND(" ",Sheet1!A2)+1,
FIND("(",Sheet1!A2)-FIND(" ",Sheet1!A2)-1)&
UPPER(MID(Sheet1!A2,6,FIND(" ",Sheet1!A2)-6))
--ron
|