Extract given and surname a string
 

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

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

Extract given and surname a string
 

Exactly what I needed, many thanks Ron.

Cheers

Bob

"Ron Rosenfeld" wrote in message
...
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

Extract given and surname a string
 

On Sun, 29 Apr 2007 12:16:32 +1000, "Bob Maloney" wrote:

Exactly what I needed, many thanks Ron.

Cheers

Bob

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