|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
December 10th, 2006, 06:11 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
Separating text
I need to separate a long list of names into First and Surname.
The trouble is I do not know how to use FIND to find the last space in the text string. Can you help? (I see I shall have to use Replace to get rid of White Space first.) Rev and Mrs R Grosse Helen & John Hadland John and Valerie Hall Peter and Pat Hall Peter and Linda Halton Francis Hookham (That's me!) 
|
|
#2
December 10th, 2006, 07:21 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Separating text
Get the first name:
=LEFT(A1,FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1) Get the last name: =RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) "Francis Hookham" wrote: I need to separate a long list of names into First and Surname. The trouble is I do not know how to use FIND to find the last space in the text string. Can you help? (I see I shall have to use Replace to get rid of White Space first.) Rev and Mrs R Grosse Helen & John Hadland John and Valerie Hall Peter and Pat Hall Peter and Linda Halton Francis Hookham (That's me!)
|
|
#3
December 10th, 2006, 08:35 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Separating text
Hi Francis
If Trailing/Leading spaces might be an issue, you could use this adaptation: First name(s) =LEFT(TRIM(A1),FIND("@",SUBSTITUTE(TRIM(A1)," ","@",LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))))) Surname =TRIM(REPLACE(TRIM(A1),1,LEN(B1),"")) Hope this helps! Richard Francis Hookham wrote: I need to separate a long list of names into First and Surname. The trouble is I do not know how to use FIND to find the last space in the text string. Can you help? (I see I shall have to use Replace to get rid of White Space first.) Rev and Mrs R Grosse Helen & John Hadland John and Valerie Hall Peter and Pat Hall Peter and Linda Halton Francis Hookham (That's me!)
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
