• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Finding position of the first integer in alpha- numeric string

Concatenating "0123456789" to the string tested will ensure each of {0,1,2,3,4,5,6,7,8,9} in Find function evaluates to numeric value and not error.

So if there was no numeric value in a string...
=MIN(FIND({0,1,2,3,4,5,6,7,8,9},"AAAA"&"0123456789")

Will evaluate to...
=MIN({5,6,7,8,9,10,11,12,13,14})
=5
But since Length of the string is 4, you can tell that there's no number in the string.

Without concatenation... if string is missing any number between 0-9, Find array will have 1 or more #Value error and MIN will always evaluate to #Value error.

There are other ways, but above is one of more elegant formula that I know.

Edit: Using concept from formula in below post you can shorten it to...
=MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&56^7))
 
Last edited:
Here's another formula using similar concept. But will need to be confirmed as array (CTRL + SHIFT + ENTER).
=MIN(FIND(ROW($1:$10)-{1},A2&56^7))
 
Another one (non-CSE) in similar logic,

=AGGREGATE(15,6,FIND({0,1,2,3,4,5,6,7,8,9},A1&1),1)

or,

=AGGREGATE(15,6,FIND(ROW($1:$10)-1,A1&1),1)

Regards
 
Last edited:
This formula (normally entered):
=MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))
is equivalent of (in a sense) of this CSE (array formula)
=IFERROR(MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2)),0)

This construct lets you avoid array entry.
 
Chihiro said:
Concatenating "0123456789" to the string tested will ensure each of {0,1,2,3,4,5,6,7,8,9} in Find function evaluates to numeric value and not error.

So if there was no numeric value in a string...
=MIN(FIND({0,1,2,3,4,5,6,7,8,9},"AAAA"&"0123456789")

Will evaluate to...
=MIN({5,6,7,8,9,10,11,12,13,14})
=5
But since Length of the string is 4, you can tell that there's no number in the string.

Without concatenation... if string is missing any number between 0-9, Find array will have 1 or more #Value error and MIN will always evaluate to #Value error.

There are other ways, but above is one of more elegant formula that I know.

Edit: Using concept from formula in below post you can shorten it to...
=MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&56^7))
Click to expand...
Thank you @Chihiro. This is very helpful. Excel is sweet
 
Chihiro said:
Here's another formula using similar concept. But will need to be confirmed as array (CTRL + SHIFT + ENTER).
=MIN(FIND(ROW($1:$10)-{1},A2&56^7))
Click to expand...
Thanks @Chihiro. A small follow on: what is the essence of wrapping 1 around curly braces please? I have tried entering 1 and it works fine. Thanks
 
Chihiro said:
Concatenating "0123456789" to the string tested will ensure each of {0,1,2,3,4,5,6,7,8,9} in Find function evaluates to numeric value and not error.

So if there was no numeric value in a string...
=MIN(FIND({0,1,2,3,4,5,6,7,8,9},"AAAA"&"0123456789")

Will evaluate to...
=MIN({5,6,7,8,9,10,11,12,13,14})
=5
But since Length of the string is 4, you can tell that there's no number in the string.

Without concatenation... if string is missing any number between 0-9, Find array will have 1 or more #Value error and MIN will always evaluate to #Value error.

There are other ways, but above is one of more elegant formula that I know.

Edit: Using concept from formula in below post you can shorten it to...
=MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&56^7))
Click to expand...
A follow on question @Chihiro. In your example, the answer is 5, while in fact there is no number in the original text string. Let us figure out how to get blank in cases like this one.
 
shrivallabha said:
This formula (normally entered):
=MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))
is equivalent of (in a sense) of this CSE (array formula)
=IFERROR(MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2)),0)

This construct lets you avoid array entry.
Click to expand...
For =IFERROR(MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2)),0), if cell A2 has AAAA for instance, the formula would return 0. Let us figure out how to tweak the formula so that we get blank instead. Formatting probably?
 
You could do that. My formula is incorrect (should've checked before posting). It should be like below:
=MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A2),""))
 
Last edited:
{} around 1 is just left over from original construct, where I was using array, which I forgot to remove ;)

=MIN(FIND({0,1,2,3,4,5,6,7,8,9},"AAAA"&"0123456789")
Click to expand...

You can just nest it in If statement like...
Assuming string is in A2.
=IF(LEN(A2)>MIN(FIND({0,1,2,3,4,5,6,7,8,9},"AAAA"&56^7),MIN(FIND({0,1,2,3,4,5,6,7,8,9},"AAAA"&56^7),"")

NOTE: Unless you are using MIN(FIND()) construct inside another formula, Shrivallabha's construct is more efficient.
 
You must log in or register to reply here.
Back
Top