I am trying to figure out what exactly excel is doing with these two functions.
I have many rows of strings in a single column. I want to pull out the first numeric in those strings. The first number can be 0,1,2,3...,9 and the total length of the string varies. I want to find the first number and return it and everything after it. I found the following code on google while searching for how to find the first numeric in a string but I don't understand how it works. The only changes I've made is that I added a failure action to the IF-statement.
=MIN(IF(ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},A1))=TRUE,FIND({1,2,3,4,5,6,7,8,9,0},A1),"N/A"))
As I read this, it says the following: Are any numbers in the array {1,2,3,4,5,6,7,8,9,0}
found in my string? If there are then ISNUMBER() = TRUE
will pass and the code will execute the second FIND()
function. It then passes the results of the second FIND()
function (i.e. the positions of all the numerics in the string) to the MIN()
function which takes the minimum (i.e. the first position).
This does work correctly but what I don't understand is if I do it without the IF-statement, then it gives me a #VALUE
error. In other words, if I do this
=MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1))
I get #VALUE
in the output cell. Why? I just took out the IF-statement as far as I can see.
Can anyone explain what I am missing here. Is it the way excel passes information from the IF()
function?