Placing word into a seperate cell

[Guest]

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

 

[Trevor Shuttleworth]

One way:

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

Regards

Trevor

 

[Rick Rothstein \(MVP - VB\)]

One way:

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

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

 

[Guest]

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

 

[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

 

[Trevor Shuttleworth]

Neat ... thank you.

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

 

[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.

 

[Guest]

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

thanks and regards

 

[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

 

[Guest]

Good stuff for next time

thanks David

regards