Placing word into a seperate cell

D

Daniel- Sydney

Hi

I have Excel 2003 SP2.

I have a spreadsheet with one column, each cell contains several words, I
need the first word in each cell to be moved into a cell in an adjacent
column.

Can I do this automatically?

thanks
 
T

Trevor Shuttleworth

One way:

=IF(ISERROR(FIND(" ",A1)),A1,(LEFT(A1,FIND(" ",A1)-1)))

Regards

Trevor
 
R

Rick Rothstein \(MVP - VB\)

One way:
=IF(ISERROR(FIND(" ",A1)),A1,(LEFT(A1,FIND(" ",A1)-1)))
Click to expand...

You can eliminate the need for the error check and simply the expression by
making sure FIND always has a space to find...

=LEFT(A1&" ",FIND(" ",A1&" ")-1)

Rick
 
D

Daniel- Sydney

Trevo

thanks, it works to an extent but the result is merging cells b1 and b2, b3
and b4 and so on, so the first word in cells a2 and a4, and so on, are not
being copied over.

What have I done wrong?

thanks
 
J

Jeffrey W. Smith

For quick and dirty, you can try experimenting with the Data | Text to
Column feature, using a delimiters of Space. Then if your data is in column
A, specify in Step 3 that the Destination is in column B. The first word
will then appear in Column B and you can delete the columns from C onwards
....

HTH,

Jeff
 
T

Trevor Shuttleworth

Neat ... thank you.


Rick Rothstein (MVP - VB) said:
You can eliminate the need for the error check and simply the expression
by making sure FIND always has a space to find...

=LEFT(A1&" ",FIND(" ",A1&" ")-1)

Rick
Click to expand...
 
T

Trevor Shuttleworth

No idea really. Did you copy and paste the formula or did you retype it ?

You might want to try Rick's solution ... should be same result but a neater
way of doing it.
 
D

Daniel- Sydney

Thanks, that did it,
and I have learned from the other replies.

thanks and regards
 
D

David McRitchie

Text to Columns is not a good solution, unless you have only
two words the result is worse than what you started with.

A macro solution avoids the messy clean up after using
formulas to extract the first word into a second column
and the rest into a third column, then convert to constants
and remove the original column.

I have two macros on my join.htm page that are helpful
for separating the first word or the last word, placing the
rest of the string (less the space) into the next column.
http://www.mvps.org/dmcritchie/excel/join.htm#septerm
http://www.mvps.org/dmcritchie/excel/join.htm#seplastterm
and of course the opposite is to join the contents of several columns
http://www.mvps.org/dmcritchie/excel/join.htm#join
If not familiar with installing macros see
http://www.mvps.org/dmcritchie/excel/getstarted#havemacro
 
Top