How to locate the first digit in a cell, explained
 

Several years ago, in a post that seems now closed to replies, Ron
Rosenfeld posted a solution to someone's request for a formula that
found the location of the first digit in each cell:



Array-entered formula:

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

To array-enter a formula, hold down <ctrl<shift while hitting <enter.
XL will place curly brackets {...} around the formula.

A colleague wanted to know how to do this and I tracked down the
post. But it didn't satisfy, quite (though it works perfectly); I
wanted to understand it, too. So I deconstructed it and wrote it up
for my colleague, then figured I would post it here as well for
posterity. So here is my explanation of the above formula.

The expression

INDIRECT("1:"&LEN(A10))

identifies the rows between the first and the one whose number is the
length of the text in A10. Why in the world would we want that?
Well, when we put ROW() around it and put it into an array formula, it
produces a series of integers, because ROW() returns an integer. So
that's a tricky way to get (in this case) the series {1;2;3;4;5;6}.
The formula reduces to:

{=MATCH(TRUE,ISNUMBER(--MID(A10,{1;2;3;4;5;6},1)),0)}

That's starting to make more sense.

The MID() of A10, for each element in the series 1-6, one character
long - that's just identifying the individual characters in turn. And
that's why the LEN(A10) was used to determine the series length. So
we reduce it to

{=MATCH(TRUE,ISNUMBER(--{"a"; "b"; "c"; "1"; "2"; "3"}),0)}

in the specific case where A10 holds "abc123". What's that "--" all
about?

Well, ISNUMBER("1") is false. But -"1" is -1, whereas -"a" produces a
#VALUE error; the double minus simply restores the original sign
(which in fact is unnecessary in this case because ISNUMBER(-1) is
also TRUE).

We're getting there.

{=MATCH(TRUE,{FALSE; FALSE; FALSE; TRUE; TRUE; TRUE},0)}

That says, find the first value in the series that matches TRUE and
return its position; the zero at the tail end I think signifies that
we require an exact match.

Got all that? This will be on the quiz... :-)

Peace,
--Carl

How to locate the first digit in a cell, explained
 

This formula by Domenic is much more efficient

=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789") )

No need for array entering either

--
Regards,

Peo Sjoblom


wrote in message
ps.com...
Several years ago, in a post that seems now closed to replies, Ron
Rosenfeld posted a solution to someone's request for a formula that
found the location of the first digit in each cell:



Array-entered formula:

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

To array-enter a formula, hold down <ctrl<shift while hitting <enter.
XL will place curly brackets {...} around the formula.

A colleague wanted to know how to do this and I tracked down the
post. But it didn't satisfy, quite (though it works perfectly); I
wanted to understand it, too. So I deconstructed it and wrote it up
for my colleague, then figured I would post it here as well for
posterity. So here is my explanation of the above formula.

The expression

INDIRECT("1:"&LEN(A10))

identifies the rows between the first and the one whose number is the
length of the text in A10. Why in the world would we want that?
Well, when we put ROW() around it and put it into an array formula, it
produces a series of integers, because ROW() returns an integer. So
that's a tricky way to get (in this case) the series {1;2;3;4;5;6}.
The formula reduces to:

{=MATCH(TRUE,ISNUMBER(--MID(A10,{1;2;3;4;5;6},1)),0)}

That's starting to make more sense.

The MID() of A10, for each element in the series 1-6, one character
long - that's just identifying the individual characters in turn. And
that's why the LEN(A10) was used to determine the series length. So
we reduce it to

{=MATCH(TRUE,ISNUMBER(--{"a"; "b"; "c"; "1"; "2"; "3"}),0)}

in the specific case where A10 holds "abc123". What's that "--" all
about?

Well, ISNUMBER("1") is false. But -"1" is -1, whereas -"a" produces a
#VALUE error; the double minus simply restores the original sign
(which in fact is unnecessary in this case because ISNUMBER(-1) is
also TRUE).

We're getting there.

{=MATCH(TRUE,{FALSE; FALSE; FALSE; TRUE; TRUE; TRUE},0)}

That says, find the first value in the series that matches TRUE and
return its position; the zero at the tail end I think signifies that
we require an exact match.

Got all that? This will be on the quiz... :-)

Peace,
--Carl

How to locate the first digit in a cell, explained
 

Hi, Peo,

=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789") )

Thanks; I'm glad to know about it.

Peace,
--Carl

How to locate the first digit in a cell, explained
 

"Peo Sjoblom" wrote...
This formula by Domenic is much more efficient

=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789" ))
....

FWIW, Aladin Arkyurek had posted a variant using FIND two months
before Domenic posted his using SEARCH, and there's an old Japanese
language post (3 years before Aladin's and Domenic's) showing this
exact idiom in the Google Groups archive.

Almost every Excel question has been asked and answered several times
before.

How to locate the first digit in a cell, explained
 

This formula doesn't seem to work correctly. If I have in cell A2 the
text XX the number 3 is returned. But there's no number in that
string of characters.

What am I missing?

The array formula proposed by the OP, however, reliably returns #N/A
if there is no number in the cell.

Dave

On May 10, 1:51 pm, Harlan Grove wrote:
"Peo Sjoblom" wrote...
This formula by Domenic is much more efficient

=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789" ))

...

FWIW, Aladin Arkyurek had posted a variant using FIND two months
before Domenic posted his using SEARCH, and there's an old Japanese
language post (3 years before Aladin's and Domenic's) showing this
exact idiom in the Google Groups archive.

Almost every Excel question has been asked and answered several times
before.

How to locate the first digit in a cell, explained
 

That formula is more or less designed to be used as an argument for
extracting a number from a string. But, it can be used as is with a little
tweak.

The formula is concatenating the digits to the string. So, if:

A1 = XX

=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789") )

This portion:

A1&"0123456789"

Evaluates to:

XX0123456789

The 0 being in position 3

Basically, the expression: A1&"0123456789" is used as an error trap. So, to
correct the formula you'd just need to add a length test:

=IF(MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"012345678 9"))LEN(A1),"",MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A 1&"0123456789")))

Biff

"Dave F" wrote in message
oups.com...
This formula doesn't seem to work correctly. If I have in cell A2 the
text XX the number 3 is returned. But there's no number in that
string of characters.

What am I missing?

The array formula proposed by the OP, however, reliably returns #N/A
if there is no number in the cell.

Dave

On May 10, 1:51 pm, Harlan Grove wrote:
"Peo Sjoblom" wrote...
This formula by Domenic is much more efficient

=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789" ))

...

FWIW, Aladin Arkyurek had posted a variant using FIND two months
before Domenic posted his using SEARCH, and there's an old Japanese
language post (3 years before Aladin's and Domenic's) showing this
exact idiom in the Google Groups archive.

Almost every Excel question has been asked and answered several times
before.

How to locate the first digit in a cell, explained
 

Dave F wrote:
This formula doesn't seem to work correctly. If I have in cell A2 the
text XX the number 3 is returned. But there's no number in that
string of characters.

What am I missing?
....

It returns a character position beyond the end of the string. Compare
the following two formula that would return the first decimal numeral
in A10, if any, else "".

[array formula]
=IF(ISNA(MATCH(TRUE,ISNUMBER(--MID(A10,ROW(INDIRECT("1:"&
LEN(A10))),1)),0)),"",MID(A10,MATCH(TRUE,ISNUMBER(--MID(A10,
ROW(INDIRECT("1:"&LEN(A10))),1)),0),1))

with

[regular formula]
=MID(A10,MIN(FIND({0;1;2;3;4;5;6;7;8;9},A10&"01234 56789")),1)

Which looks better to you?

How to locate the first digit in a cell, explained
 

So, to correct the formula you'd just need to add a length test

Or, use Harlan's formula where he expands it to use the MID function.

Biff

"T. Valko" wrote in message
...
That formula is more or less designed to be used as an argument for
extracting a number from a string. But, it can be used as is with a little
tweak.

The formula is concatenating the digits to the string. So, if:

A1 = XX

=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789") )

This portion:

A1&"0123456789"

Evaluates to:

XX0123456789

The 0 being in position 3

Basically, the expression: A1&"0123456789" is used as an error trap. So,
to correct the formula you'd just need to add a length test:

=IF(MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"012345678 9"))LEN(A1),"",MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A 1&"0123456789")))

Biff

"Dave F" wrote in message
oups.com...
This formula doesn't seem to work correctly. If I have in cell A2 the
text XX the number 3 is returned. But there's no number in that
string of characters.

What am I missing?

The array formula proposed by the OP, however, reliably returns #N/A
if there is no number in the cell.

Dave

On May 10, 1:51 pm, Harlan Grove wrote:
"Peo Sjoblom" wrote...
This formula by Domenic is much more efficient

=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789" ))

...

FWIW, Aladin Arkyurek had posted a variant using FIND two months
before Domenic posted his using SEARCH, and there's an old Japanese
language post (3 years before Aladin's and Domenic's) showing this
exact idiom in the Google Groups archive.

Almost every Excel question has been asked and answered several times
before.