Identifying spaces in a string

[bwblick]

Anyone know how to recognize when a space exists in a cell that contain
only text. Or, better yet, how can I pull data out of a text string u
until a space is found

 

[Frank Kabel]

Hi
try
=LEFT(A1,FIND(" ",A1)-1)

 

[Len Canders]

is there a way to use the RIGHT or another function somewhat
similarly?

for example, i have names in the format Lastname, Firstname M (where
the "M" stands for middle initial). i can use your formula with a
comma in the " ": =LEFT(A1,FIND(",",A1)-1) to get Lastname, but how
can i get the firstname and initial?

if i try =RIGHT(A2,FIND(",",A2)-1) on my example i get this result:
stname M

other results with differing size names are all over the place so the
results seem to have something to do with the siz eof the name itself,
but i don't know how to adjust for this.

thanks in advance.

 

[Frank Kabel]

Hi
see:
http://www.cpearson.com/excel/FirstLast.htm

 

[Len Canders]

thank you very much. these formulas do work very well. my own problem
is that while i have an immediate solution i simply don't understand
how they work so can't apply it to other comparable situations. maybe
the auditing tool will help? i do appreciate your response, but also
want to learn.

having said that, may i request help with a related question? here it
is:

i now have last names such as Jones_Jr or Smith_Sr

what i want to do is make these:

1) Jones,_Jr. and Smith,_Sr. (adds comma after basic lastname and a
"." after Jr or Sr)

and

2) JONES,_Jr. and SMITH,_Sr. (adds comma after basic lastname, "."
after Jr or Sr, and capitalizes all letters in basic lastname)

(the underscore (_) shows a space which is sometimes hard to see.)

sorry if these are really basic, but thanks again for any help.

 

[Myrna Larson]

Use search and replace. Replace space with ,space; replace Sr with Sr.;
replace Jr with Jr. I could do a final "cleanup": replace .. with . and two
spaces with one space, in case there are any doubled periods or spaces