extract numbers from cell problem

[bill gras]

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

 

[bj]

how are you extracting the numbers?

 

[Ron Rosenfeld]

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

I would guess there's some logic flaw in the method you are using to extract
the numbers; very hard to tell when you do not post the method of extraction,
however.


--ron

 

[bill gras]

Hi all
Thank you for your reply

I used this formula in column "L" :-=LEFT(B1,FIND(" ",B1)) result: L

1 5-14

2 11-14

3 1-12

4 10-16

5 1-12
I used for column C this formula:
=VALUE(LEFT(L1,FIND("-",L1)-1))

I used for column D this formula:
=VALUE(IF(ISERROR(FIND("-",L1)),L1,MID(L1,FIND("-",L1,1)+1,99)))

I used for column E this formula:
=VALUE(RIGHT(B1,FIND(" ",B1)-1))

I hope you can help me with this

bill

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

Hi all
Thank you for your reply

I used this formula in column "L" :-=LEFT(B1,FIND(" ",B1)) result: L

1 5-14

2 11-14

3 1-12

4 10-16

5 1-12
I used for column C this formula:
=VALUE(LEFT(L1,FIND("-",L1)-1))

I used for column D this formula:
=VALUE(IF(ISERROR(FIND("-",L1)),L1,MID(L1,FIND("-",L1,1)+1,99)))

I used for column E this formula:
=VALUE(RIGHT(B1,FIND(" ",B1)-1))

I hope you can help me with this

bill

A little more research reveals that the following regular expression is
'better' for matching your numbers.

"[0-9]*\.?[0-9]+"

So the formula would be:

=--REGEX.MID($A1,"[0-9]*\.?[0-9]+",COLUMN()-CELL("col",$A1))


--ron