formula to remove text at end of string???

[richardeallen]

Hi

I am importing some data into a spreadsheet from a .txt file.
The data is imported with the unit identifier as text where as i just need the number.
i.e.
imported value: 1316 ML
Required value: 1316

The values imported change i.e. could be 10 ML or 50000 ML therefore i cant just use a LEFT or RIGHT formula.

Any ideas?

Must be a simple fix to this.

Thanks

[richardeallen]

It must do this by linking a formula to the cell so that it is automatic.
I cant just select the data and select text to columns etc.

Many thanks

[oldchippy]

Try something like this,

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

[VBA Noob]

Select the Column > press ctrl + H > find box press the space bar once for the space then enter ML > leave the replace box empty and replace all

Or with formulas

=LEFT(A1,FIND(" ",A1))

or

=SUBSTITUTE(A1," ML","")

VBA Noob

[richardeallen]

Thanks very much. The substitute formula worked a treat.

Cheers