Seperate letters and numbers

[cdevlinrushe]

Hi,
I have a value for a volume eg. 24 L and I want to split this so I just have the volume unit (in this case L). The values are delimited so I can't use string functions.

24 L is in the cell R2.
There can also be values such as 16000 KG.
There is always a space between the value and the unit if that helps any.

 

[AlanY]

are they always seperated by a space ""?

 

[cdevlinrushe]

Yes

 

[AlanY]

in that case

for the numbers part, =LEFT(A1,FIND(" ",A1)-1)

and the letters, =RIGHT(A1,LEN(A1)-FIND(" ",A1))

 

[cdevlinrushe]

I now realise why I am receiving an error.
The cell reads 24 L but when I click on the cell it actually contains only 24 (in the function bar)
Any way I can resolve this without using a formula?

 

[AlanY]

try this

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

 

[AlanY]

I now realise why I am receiving an error.
The cell reads 24 L but when I click on the cell it actually contains only 24 (in the function bar)
Any way I can resolve this without using a formula?

you can use excel's built-in Text-to-Column function using space as the "delimited".

 

[Noodleski]

Does this mean they added the unit of measure as a customized cell format for each individual cell??

Kill it. Kill it with fire.