Deleting everything after the first space in a cell

[oveste]

I need to delete all data after the first space in every row of a column. Any clues?

 

[MrKowz]

Assuming your data is in Col A, use the following formula in another column:

=LEFT(A1,Find(" ",A1)-1)

Copy down. If you want to compeltely replace the data in Col A, then copy>paste special these new values onto Col A.

 

[oveste]

Thanks,
I new it had to be a LEFT or RIGHT function. I was unfarmiliar with the FIND function.

 

[Scott Huish]

If you want to avoid the error if there is no space:

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

 

[Laustad]

I have tried the formula posted and after applying it to a doc that i am working on it returns a cyclic refrence and gives me a zero, i have text in column E that i wish to apply this to that starts at row 105, examples of the data are,

ABST22112 2" x 2" x 1-1/2" ABS SANITARY TEE
XPVCT2112112 2"x11/2"x11/2" PVC DWV SANITARY TEE
XPVCT21122 2"x 1-1/2"x 2" PVC DWV SANITARY TEE

and i am editing the formula to be =LEFT(F103,FIND(" ",F103)-1) just not sure what i am doing wrong. fairly new to Excel Formulas.

 

[Vtikhon]

Try this: =LEFT(E103,FIND(" ",E103&" ")-1)

 

[taurean]

The cyclic reference comes when you insert a formula which includes the own address of the cell. e.g. you are inserting formula stated above in the same cell F103. If it is so (especially if your data is in column E) then you need to change it like:
=LEFT(E103,FIND(" ",E103)-1)

 

[Laustad]

Sweet, worked like a Charm!!! thanks guys save me hours of work :D

 

[stct80]

Assuming your data is in Col A, use the following formula in another column:

=LEFT(A1,Find(" ",A1)-1)

Copy down. If you want to compeltely replace the data in Col A, then copy>paste special these new values onto Col A.

Hi,
I have a similar requirement, but need to delete everything after the 2nd space in a cell.

Any advise?

 

[Rick Rothstein]

Hi,
I have a similar requirement, but need to delete everything after the 2nd space in a cell.

Give this formula a try (also works if there are not two spaces...returns what's there)...

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