Parse at the first numeric value
 

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

Parse at the first numeric value
 

On Wed, 21 Feb 2007 12:57:03 -0800, Steve Stad
wrote:

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

Parse at the first numeric value
 

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" wrote:

On Wed, 21 Feb 2007 12:57:03 -0800, Steve Stad
wrote:

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

Parse at the first numeric value
 

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

"Ron Rosenfeld" wrote:

On Wed, 21 Feb 2007 12:57:03 -0800, Steve Stad
wrote:

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

Parse at the first numeric value
 

Daddylonglegs,

Thanks your formula worked.

"daddylonglegs" wrote:

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" wrote:

On Wed, 21 Feb 2007 12:57:03 -0800, Steve Stad
wrote:

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

Parse at the first numeric value
 

On Mon, 26 Feb 2007 12:53:45 -0800, Steve Stad
wrote:

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