On Sun, 22 Jan 2006 18:21:02 -0800, "Francis WF Lee" <Francis WF
wrote:

I need 1.6 from 1.6mm² and 20.23 from 20.23mm²

1) I cannot use RIGHT function to extract as number starts from left.
2) I cannot use LEFT function as number is not fix length
3) I cannot use MID function as num_start refererence is from left not right

Following also some example which difficult to extract
1) 100.2Volts, 15Volts
2) 20m/s, 1.25m/s, 10.0m/s
3) 1.235mØ, 34.0mØ

In your second set, are all the values on each line in one cell?

If so, this seems like a good spot to use "regular expressions".

First, download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

I set up the following data:

A1: 1.6mm
A2: 20.23mm
A3: 100.2Volts, 15Volts
A4: 20m/s, 1.25m/s, 10.0m/s
A5: 1.235mØ, 34.0mØ

B1: =REGEX.MID($A1,"(\d+|\.)+",COLUMNS($A:A))

Copy/Drag down to B5. Then select B1:B5 and copy/drag across as far as needed.
In the example you give, that would be three columns.

The "COLUMNS()" argument returns a '1' in the first column, and increments as
you drag across. This is the "index" of the number in the cell in column A.
So, for example:

B4: 20
C4: 1.25
D4: 10.0

The function returns a the number as a string. Where there are fewer values
than the index, it returns an empty string.

To convert the strings to "real numbers", precede the function by a double
unary:

=--REGEX.MID($A1,"(\.|\d)+",COLUMNS($A:A))

However, in the cells with "empty strings", this will return a #VALUE error.
To get rid of that, if it is a problem, you could either use Conditional
Formatting to hide the error, or check for an error in the formula:

=IF(ISERR(-REGEX.MID($A1,"(\.|\d)+",COLUMNS($A:A))),
"",--REGEX.MID($A1,"(\.|\d)+",COLUMNS($A:A)))


--ron