CODEWITHSUNDEEP
excelexcel-formula
Magesh
Magesh

Reputation: 21

Using if or mid and find in excel

Lets say column A1 contains 12769745. I want to extract 7697 from column A1 and want to use if statement. After the extract if it contains 7697, it should return one else return two. How to accomplish this in a single formula.

Upvotes: 0

Views: 130

Answers (2)

Glitch_Doctor
Glitch_Doctor

Reputation: 3034

=IF(MID(A1,3,4)="7697",1,2)

This is assuming that the the length will always be 8 digits.

You can adjust a more dynamic formula to be something like the below, though you will need to handle odd lengths by rounding up/down:

=IF(MID(A1,LEN(A1)*0.25+1,LEN(A1)*0.5)="*7697*",1,2)

Upvotes: 0

QHarr
QHarr

Reputation: 84465

Maybe, this would go in B1

=IF(ISNUMBER(SEARCH(7697,A1,1)),1,2)

Upvotes: 1

Related Questions