Hi,
I'm trying to come up with a formula that will recognize the first 3 digits only of a number within a string. The numbers in the string range from 3 to 6 digits in length. If the numbers are recognized, a yes answer is returned. If not, a no answer.
So, for example - say I input 211112. The digits 211 are included in the string, so I would like it to return a yes answer.
Thanks in advance for your help!
Do you mean:
=IF(LEFT(A1,3)+0=211,"Yes","No")
or
=IF(ISNUMBER(SEARCH(211,A1)),"Yes","No") for search anywhere in the string.
you can replace 211 with a cell reference containing the search number.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Thank you for your response. Just for clarification, in your first formula, the 211 I would need to change to a "string" of numbers (ex. H28:H80).
I tried reflecting it as such and didn't have any luck:
=IF(LEFT('2-Info'!C9,3)+0=H28:H80,"Yes","No")
Is the above formula going to pick up only the first three digits in both the manual input number and the string of numbers? That is what I need it to do.
Thanks again
Maybe you want...
=IF(ISNUMBER(MATCH(LEFT('2-Info'!C9,3)+0,H28:H80,0)),"Yes","No")
This looks for the Left 3 digits of C9 in a range of 3 digit numbers contained in H28 to H80
Perfect, thank you!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks