split cell values

[cowfishrule]

trying to pull apart data in a cell.
this has to do with names.
first (space) last name are all in column a.
need to split that apart and have first name remain in "a" and last name to show in "b"

any clues?

 

[SkipVought]

If ALL the names are first (space) last name ...

then check out Data > Text to columns ... DELIMITED - check SPACE

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[bluedragon2]

Just hope you do not have names like:
John Van Der Hoffman
Jim Micheal James Simmons
Timothy Allen Jacobs III





[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[yogia]

Just hope you do not have names like:
1) John Van Der Hoffman
2) Jim Micheal James Simmons
3) Timothy Allen Jacobs III

Well as far as the first two names are concerned ... if the names are in column A, then

for First Name in cell B1 ... =LEFT(A1,FIND(" ",A1)-1)

and the Last Name in cell C1 ... =MID(A1,LOOKUP(9.9E+307,FIND(" ",A1,ROW($1:$30)))+1,255)








Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[bluedragon2]

Well, Yogia, names have been talked about a lot in this forum. Will you guarentee your formula will work for all name combinations, or would you suggest that the data be entered in the correct format?

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[yogia]

Well, Yogia, names have been talked about a lot in this forum. Will you guarentee your formula will work for all name combinations, or would you suggest that the data be entered in the correct format?

The formulas I have proposed wil pick up the First word, and the Last word in a NAME ... so as long as those conditions are met, the formulas should provide the right answer -- and as in the case of the third NAME in your post, the Last name is not the last word -- so there we go.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[xlbo]

I guess this can only be answered by the OP - criteria needs to be set as to how the names need to be displayed...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question