Home |
Search |
Today's Posts |
#1
|
|||
|
|||
searching for a space " "
I have a colum of data with names both first name and last. is there a
formula to pull out the first name and one to pull out the last name? a cell might current read "John Smith" i want to separate the name so i have one field reading "John" and the next field reading "Smith". The names will vary in character length so i cannot use a simple left() command. |
#2
|
|||
|
|||
Try this with the name in B5
First word =IF(ISERR(FIND(" ",B5)),B5,IF(RIGHT(LEFT(B5,FIND(" ",B5)-1))=",",LEFT(B5,FIND(" ",B5)-2),LEFT(B5,FIND(" ",B5)-1))) All except first word =IF(ISERR(FIND(" ",B5)),"",MID(B5,FIND(" ",B5)+1,1024)) See also DateRefiner http://www.rondebruin.nl/datarefiner.htm -- Regards Ron de Bruin http://www.rondebruin.nl "wahamler" wrote in message ... I have a colum of data with names both first name and last. is there a formula to pull out the first name and one to pull out the last name? a cell might current read "John Smith" i want to separate the name so i have one field reading "John" and the next field reading "Smith". The names will vary in character length so i cannot use a simple left() command. |
#3
|
|||
|
|||
What is the make-up of the majority of your data?
If you really have just 2 names, you can use TTC (Text To Columns), where an "odd-ball" (von Dunst ) could be handled manually. Check out the Help files for this function, which yields (returns) actual data within the cells, and not formulas. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "wahamler" wrote in message ... I have a colum of data with names both first name and last. is there a formula to pull out the first name and one to pull out the last name? a cell might current read "John Smith" i want to separate the name so i have one field reading "John" and the next field reading "Smith". The names will vary in character length so i cannot use a simple left() command. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I find and replace "white space" in a cell in Excel? | Excel Worksheet Functions | |||
Extra space after imported numbers | Excel Discussion (Misc queries) | |||
space between text strings with concatenate | Excel Discussion (Misc queries) | |||
Trim function doesn't clean out ASCII Code 160 (Space) | Excel Worksheet Functions | |||
Removing a space within a cell | Excel Worksheet Functions |