Parse at the first numeric value

[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

 

[Ron Rosenfeld]

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

=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

 

[Guest]

assuming the number is at the end as per your example

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

 

[Guest]

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

 

[Guest]

Daddylonglegs,

Thanks your formula worked.

assuming the number is at the end as per your example

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

 

[Ron Rosenfeld]

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

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