On Thu, 20 Oct 2005 07:26:19 -0700, bill gras
wrote:

I have a column with numbers that I split up and extract from e g:
A
1 5-14 3.2L
2 11-14*3.6L
3 +1-12 0.5L
4 10-16 5.1L
5 +1-12*0.2L
I used a function to delete the +,*,L as follows:
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"+",""),"*" ," "),"L","")
that leaves me with column "B" as follows
B
1 5-14 3.2
2 11-14 3.6
3 1-12 0.5
4 10-16 5.1
5 1-12 0.2
then I extract from column "B" numbers as follows
column column column
C D E
1 5 14 3.2
2 11 14 3.6
3 1 12 0.5
4 10 16 #value! should be 5.1
5 1 12 0.2

A1 has the same number content as A4 , yet A4 returns an error
I tried different formats but no luck
Has it something to do with B1 has 7 caracters and B4 has 8 caracters ?

can some one please help
thanks
bill

Well, I've been fooling around with Regular Expressions and I'm not entirely
sure what the optimum one is for this problem. Perhaps Harlan will chime in.

I suspect my use of the [dot] in the character class is not 'good form'. I
note that 0. and 0.0 also work in that position.

In order to use them, download and install Longre's free morefunc.xll add-in
from http://xcell05.free.fr/

The following seems to work to extract the three numbers in your strings:

=REGEX.MID($A1,"[.-99]*",1)
=REGEX.MID($A1,"[.-99]*",2)
=REGEX.MID($A1,"[.-99]*",3)


If you're entering these formulas in columns adjacent to your data, and your
data starts in A1, then:

B1: =REGEX.MID($A1,"[.-99]*",COLUMN()-CELL("col",$A1))

and copy/drag across to column D; and copy/drag down as needed.

If the values need to be NUMERIC and not TEXT, then precede the formula with a
double unary:

B1: =--REGEX.MID($A1,"[.-99]*",COLUMN()-CELL("col",$A1))


--ron