Extract given and surname a string

[Bob Maloney]

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

 

[Ron Rosenfeld]

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

 

[Bob Maloney]

Exactly what I needed, many thanks Ron.

Cheers

Bob

 

[Ron Rosenfeld]

Exactly what I needed, many thanks Ron.

Cheers

Bob

You're welcome. Thanks for the feedback.
--ron