Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
sperate from the space, in text, in the same cell
Hi..
Is there a way tpo seperate or extract out first or last name from the same cell? for example, if in cell A1 we have John Smith, can I able top extract out just John or Smith in cell B1? Any help will be appreciated thx -- Message posted via http://www.officekb.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
sperate from the space, in text, in the same cell
You could use the SEARCH worksheet function. This searches for a specific
character in a cell and returns the position. Combine it with LEFT or MID (or RIGHT, if you also use LEN) to get the name you want. =LEFT(A1,SEARCH(" ",A1)-1) will give you the first name =RIGHT(A1,LEN(A1)-SEARCH(" ",A1)) will give you the surname. You will have to be a little more creative if there are middle names though. Jeff "Fam via OfficeKB.com" <u18245@uwe wrote in message news:6113c58e9d8fc@uwe... Hi.. Is there a way tpo seperate or extract out first or last name from the same cell? for example, if in cell A1 we have John Smith, can I able top extract out just John or Smith in cell B1? Any help will be appreciated thx -- Message posted via http://www.officekb.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
sperate from the space, in text, in the same cell
Have you tried "Text to Columns"? Data-Text to Columns...-Delimited Check off "Space" -- Ikaabod ------------------------------------------------------------------------ Ikaabod's Profile: http://www.excelforum.com/member.php...o&userid=33371 View this thread: http://www.excelforum.com/showthread...hreadid=547113 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
sperate from the space, in text, in the same cell
Have you tried "Text to Columns"? Data-Text to Columns...-Delimited Check off "Space" -- Ikaabod ------------------------------------------------------------------------ Ikaabod's Profile: http://www.excelforum.com/member.php...o&userid=33371 View this thread: http://www.excelforum.com/showthread...hreadid=547113 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
sperate from the space, in text, in the same cell
thanks Jeff
Jeff Standen wrote: You could use the SEARCH worksheet function. This searches for a specific character in a cell and returns the position. Combine it with LEFT or MID (or RIGHT, if you also use LEN) to get the name you want. =LEFT(A1,SEARCH(" ",A1)-1) will give you the first name =RIGHT(A1,LEN(A1)-SEARCH(" ",A1)) will give you the surname. You will have to be a little more creative if there are middle names though. Jeff Hi.. Is there a way tpo seperate or extract out first or last name from the [quoted text clipped - 4 lines] Any help will be appreciated thx -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200605/1 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
sperate from the space, in text, in the same cell
I able to extract the first name name but what if the situation is like
John Smith, CFO Now I canextract the first name by =left(A1, find(" ",A1)-1) and the title by =trim(mid(a1,find(" ",A1),255)) but come is still coming with it and I can not able to resolve how to seperate the last name with the title and extract it out any help please? Fam wrote: thanks Jeff You could use the SEARCH worksheet function. This searches for a specific character in a cell and returns the position. Combine it with LEFT or MID [quoted text clipped - 16 lines] Any help will be appreciated thx -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200605/1 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
sperate from the space, in text, in the same cell
It helps to have a set of nice data but of course that isn't always what
happens. If you know, for instance, that the surname will always be between the first space and the first comma, you could do this =MID(A1,SEARCH(" ",A1)+1,SEARCH(",",A1)-SEARCH(" ",A1)-1) You have to play it by ear a little - you can use the IF function to seperate different options depending on, say, whether there is a comma in it or not. Jeff "Fam via OfficeKB.com" <u18245@uwe wrote in message news:61143504495eb@uwe... I able to extract the first name name but what if the situation is like John Smith, CFO Now I canextract the first name by =left(A1, find(" ",A1)-1) and the title by =trim(mid(a1,find(" ",A1),255)) but come is still coming with it and I can not able to resolve how to seperate the last name with the title and extract it out any help please? Fam wrote: thanks Jeff You could use the SEARCH worksheet function. This searches for a specific character in a cell and returns the position. Combine it with LEFT or MID [quoted text clipped - 16 lines] Any help will be appreciated thx -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200605/1 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
sperate from the space, in text, in the same cell
How about John van Dyke Smith Jones Sr.
or not to be as outlandish John Van Dyke, CFO To the OP, there is no generalized algorithm that is going to do what you want unless you have a specific pattern or a finite set of unique patterns that are distinct or the elements of the name are distringuishably delimited. Usually this isn't the case for names http://www.cpearson.com/excel/FirstLast.htm will give some insights. -- Regards, Tom Ogilvy "Jeff Standen" wrote in message ... It helps to have a set of nice data but of course that isn't always what happens. If you know, for instance, that the surname will always be between the first space and the first comma, you could do this =MID(A1,SEARCH(" ",A1)+1,SEARCH(",",A1)-SEARCH(" ",A1)-1) You have to play it by ear a little - you can use the IF function to seperate different options depending on, say, whether there is a comma in it or not. Jeff "Fam via OfficeKB.com" <u18245@uwe wrote in message news:61143504495eb@uwe... I able to extract the first name name but what if the situation is like John Smith, CFO Now I canextract the first name by =left(A1, find(" ",A1)-1) and the title by =trim(mid(a1,find(" ",A1),255)) but come is still coming with it and I can not able to resolve how to seperate the last name with the title and extract it out any help please? Fam wrote: thanks Jeff You could use the SEARCH worksheet function. This searches for a specific character in a cell and returns the position. Combine it with LEFT or MID [quoted text clipped - 16 lines] Any help will be appreciated thx -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200605/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Separate Text from a cell by space into other cells | Excel Worksheet Functions | |||
deleting space in cell behind text | Excel Discussion (Misc queries) | |||
How do I remove a space infront of text in a cell? | Excel Discussion (Misc queries) | |||
Location of space in a text in a cell | Excel Worksheet Functions | |||
Removing unwanted space at end of text cell | Excel Programming |