ExcelBanter - extract numbers from cell problem

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - extract numbers from cell problem (https://www.excelbanter.com/excel-worksheet-functions/51446-extract-numbers-cell-problem.html)

extract numbers from cell problem

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

extract numbers from cell problem

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

extract numbers from cell problem

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

extract numbers from cell problem

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

extract numbers from cell problem

On Thu, 20 Oct 2005 21:18:03 -0700, bill gras
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