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

One way:

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

Regards

Trevor


"Daniel- Sydney" wrote in message
...
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

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

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

"Trevor Shuttleworth" wrote:

One way:

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

Regards

Trevor


"Daniel- Sydney" wrote in message
...
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

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

"Daniel- Sydney" wrote in message
...
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

Neat ... thank you.


"Rick Rothstein (MVP - VB)" wrote in
message ...
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

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.


"Daniel- Sydney" wrote in message
...
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

"Trevor Shuttleworth" wrote:

One way:

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

Regards

Trevor


"Daniel- Sydney" wrote in
message
...
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

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

thanks and regards

"Rick Rothstein (MVP - VB)" wrote:

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

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...tm#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...rted#havemacro

--
HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm


"Jeffrey W. Smith" wrote in message ...
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

"Daniel- Sydney" wrote in message
...
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

Good stuff for next time

thanks David

regards

"Daniel- Sydney" wrote:

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