Hi
I am trying to make an exact search within a string using SEARCH formula
For eg. I have to search for 1-20 within the string "10,11,12,13,14,15" using SEARCH formula
The formula gives found result for 1 as it is finding 1 from 10
How to make the search exact??
Enter your string in cell A1 ("10,11,12,13,14,15")
Enter value you are searching for in cell B1
Enter this formula in cell C1:
Formula:
Please Login or Register to view this content.
It is using the "comma" before and after to refine the search.
Also note that it has a "comma" added before &A1 - this allows it to find the number if it's the first item in the cell
EDIT - just realised that above formula will not find the last item in the cell, so have added another "comma" after the &A1
Formula:Please Login or Register to view this content.
Last edited by kev_; 11-23-2016 at 11:01 AM. Reason: amended formula
Hi,
Found the edit very late
Thanks kev_!!!
Last edited by chullan88; 11-23-2016 at 11:41 AM.
Use MATCH and separate the values in an array instead of a single string.
<----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left
If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.
Try
=SEARCH(","&B1&",",","&A1 &",")
Hi Jacc,Originally Posted by Jacc
could you please demonstrate?
Thanks JohnTopley!!!!
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks