Parse at the first numeric value

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a list with a variable text string followed by a numeric quantity. I
would like to parse the numeric quantity into a separate field is there a
function/procedure to find and parse at the first numeric value in a string.
e.g.
Red apples 10
Fresh Florida oranges 9
Southern California tomatoes 19
Iowa potatoes 4
pears 12
 
I have a list with a variable text string followed by a numeric quantity. I
would like to parse the numeric quantity into a separate field is there a
function/procedure to find and parse at the first numeric value in a string.
e.g.
Red apples 10
Fresh Florida oranges 9
Southern California tomatoes 19
Iowa potatoes 4
pears 12
Click to expand...


=MID(A10,MATCH(TRUE,ISNUMBER(-MID(A10,ROW(INDIRECT("1:"&LEN(A10))),1)),0),255)

entered as an array formula with <ctrl><shift><enter>
--ron
 
assuming the number is at the end as per your example

=LOOKUP(9.999999999999E+307,RIGHT(A1,ROW(INDIRECT("1:"&LEN(A1))))+0)
 
Thanks Ron,

Can you explain what the ROW(INDIRECT("1:"&LEN(A10))),1)),0),255) function
does. I am getting an #N/A when I try the whole formula below. Did you test
it...i.e., does it work for you?

Thanks
Steve
 
Daddylonglegs,

Thanks your formula worked.

daddylonglegs said:
assuming the number is at the end as per your example

=LOOKUP(9.999999999999E+307,RIGHT(A1,ROW(INDIRECT("1:"&LEN(A1))))+0)
Click to expand...
 
Thanks Ron,

Can you explain what the ROW(INDIRECT("1:"&LEN(A10))),1)),0),255) function
does. I am getting an #N/A when I try the whole formula below. Did you test
it...i.e., does it work for you?

Thanks
Steve
Click to expand...

Yes, it works fine for me.

You are getting an #N/A most likely for one of two reasons:

1. You did not enter this as an **array** formula with
<ctrl><shift><enter>
2. The cell reference contains no numbers.

To be more detailed than in my initial post, to enter an array formula, after
copying or pasting the formula into the formula bar, hold down <ctrl><shift>
while hitting <enter>. Excel will place braces {...} around the formula.

The ROW(INDIRECT("1:"&LEN(A1))) generates a sequential array of numbers from 1
to the length of the text in the cell reference. This is then used to pick out
the starting number for the MID function. So it looks at each character in
turn.

The remaining ,1)),0),255) is not part of the ROW(INDIRECT function

One method of seeing how functions work is to select Tools/Formula
Auditing/Evaluate Formula which can take you step by step through most
formulas.
--ron
 
Back
Top