Wildcard for finding the first numeric digit in a cell?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

lovemuch - 17 Aug 2006 21:15 GMT

In word this is referred to "any digit" and the text you use to find it is
"^#". Is there something similar in Excel?

Thanks!

Cynthia

Reply to this Message

Bob Phillips - 17 Aug 2006 22:31 GMT

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

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> In word this is referred to "any digit" and the text you use to find it is
> "^#". Is there something similar in Excel?
>
> Thanks!
>
> Cynthia

Reply to this Message

Harlan Grove - 17 Aug 2006 23:55 GMT

lovemuch wrote...

>In word this is referred to "any digit" and the text you use to find it is
>"^#". Is there something similar in Excel?

If you need to do this often, define a name like seq referring to

=ROW(INDEX($1:$65536,1,1):INDEX($1:$65536,1024,1))

then use the array formula

=MIN(IF(ISNUMBER(-MID(x,seq,1)),seq))

to find the first decimal numeral in x. It returns 0 if there are none.

Reply to this Message