separate numbers from text

[Graham]

Hi I would like to separate the following text so the numbers are in separate
columns.

0.2 to 1.5 bar

Thanks

Graham

 

[Mike H]

Hi,

Have a look at data|Text to columns using 'space' as a delimeter

Mike

 

[Eduardo]

Hi,
Go to Data, text to columns, use space as delimiter

 

[Pete_UK]

If you want a formula method, then with your text in A1, put this in
B1:

=--LEFT(A1,SEARCH("to",A1)-1)

and this in C1:

=--SUBSTITUTE(RIGHT(A1,LEN(A1)-SEARCH("to",A1)-2),"bar","")

The -- in front of the formulae convert text values to numbers. The
formulae can be copied down if you need to.

Hope this helps.

Pete

 

[Ron Rosenfeld]

Hi I would like to separate the following text so the numbers are in separate
columns.

0.2 to 1.5 bar

Thanks

Graham

Are the numbers always as you show above?

In other words, are they always unsigned floating point numbers that will
always have an integer and a decimal portion?

If so, you could download and install Longre's free morefunc.xll add-in (Google
for a working source) and use this formula:

First Number: =REGEX.MID($A1,"\d+\.\d+",1)
Second Number: =REGEX.MID($A1,"\d+\.\d+",2)

If there were more numbers, just increment the last argument.

If the numbers might be formatted differently, post back.

The formula returns the values as a text string. If you require them to be
numeric, then precede the function with a double unary:

=--REGEX.MID($A1,"\d+\.\d+",1)

--ron