0

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?

0

Min is giving you the minimum of numbers in your string A1, so if A1 contains letters and numbers (for example 123text0) Find without If and isnumeric() will put #value in the place of characters and number in the place of digits.

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")) is putting N/A instead of #value and Min will ignore the text N/A but cannot ignore the error #value.

If() is testing the Find() for being numbers so if isnumeric is true it writes the digits, if not it will write N/A Min(1,2,3,"N/A","N/A"..)=1, but when you remove If the Min(1,2,3,#value,#value..)=#value.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.