Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract from string | Excel Discussion (Misc queries) | |||
Extract text from String | Excel Worksheet Functions | |||
extract date from string | Excel Worksheet Functions | |||
Extract sub string | Excel Worksheet Functions | |||
Extracting Surname from within a text string | Excel Worksheet Functions |