CODEWITHSUNDEEP
excelfunctionsearchexcel-formula
Constantia
Constantia

Reputation: 11

Excel find cells that partially contain a number

I Would like to return a value if a cell partially contains a number:

Example:

Range:
A1 = 90003 B

Search value:
B1 = 90003

Return: (This is what I want)
C1 = Yes

I have tried something like 

=IF(ISNUMBER(SEARCH(A1,B1)), "Yes", "No")

But this only works for text value and not for numbers.

Please assist if you know what I can use? I would preferably like to use a function. I normally just write a macro, but for this activity I would like to use a function if possible.

Upvotes: 1

Views: 77

Answers (1)

user4039065
user4039065

Reputation:

To get your formula working for the supplied sample data use,

=IF(ISNUMBER(FIND(B1, A1)), "Yes", "No")

If is an either/or situation then,

=IF(OR(ISNUMBER(FIND(A1, B1)), ISNUMBER(FIND(B1, A1))), "Yes", "No")

FIND is case-sensitive, SEARCH is not. Numbers do not have case.

Upvotes: 1

Related Questions