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

--
bill gras

[bj]

how are you extracting the numbers?

"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
>
> --
> bill gras

[Ron Rosenfeld]

On Thu, 20 Oct 2005 07:26:19 -0700, bill gras
<[email protected]> 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

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

--
bill gras


"bj" wrote:

> how are you extracting the numbers?
>
> "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
> >
> > --
> > bill gras

[Ron Rosenfeld]

On Thu, 20 Oct 2005 07:26:19 -0700, bill gras
<[email protected]> 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

[Ron Rosenfeld]

On Thu, 20 Oct 2005 21:18:03 -0700, bill gras
<[email protected]> wrote:

>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