# Solved searching for pattern in excel sheet

April 24, 2018 at 04:23:07
Specs: Windows 7
 i work for a telecom company, where we use to get premium numbers from a excel sheet which contains thousand of numbers in it. i have to search each number manually for desired search, for example if i need to search a 10 digit mobile number containing three pairs in it like (9826-131313), i will search by finding 01-01-01 to 99-99-99 manually in excel by pressing ctrl+F, i need a formula where i can search all data with a key pattern like ABABAB, so it come with result with containing three pairs in it.where A or B denote with value between 0-9 A=3 B=5 so ABABAB means 353535, hope anyone can help me out with this complication

See More: searching for pattern in excel sheet

#1
April 24, 2018 at 07:03:28
 I have to admit I'm confused.re: "10 digit mobile number containing three pairs in it like (9826-131313)i will search by finding 01-01-01 to 99-99-99 manually in excel by pressing ctrl+F"I don't see how searching for xy-xy-xy is going to find xyxyxy. Why are you using hyphens in your search string?I'm also not quite sure what your ultimate goal is. Which one of these are you trying to do:1 - When you initiate the search are you looking for a specific set of "pairs", e.g. "This time find 131313", then in your next search: "This time find 353535"?or2 - Are you searching for every number that has a sequence of 3 pairs e.g. 131313, 353535, 575757 basically trying to highlight them all at once?or3 - Something totally different?The more we know about your overall process/goal, the easier it will be for us to offer a solution without going back and forth trying to figure out what you are actually trying to accomplish.

Report •

#2
April 24, 2018 at 15:22:44
 Not completely sure if this is what your after, but here is a Condidional Formatting solution that will Highlight each phone number that has your two digit pattern in the Last Six characters of the phone number.So if you have 9826-131313, only the last six digits are compared.Conditional Formatting 20071) Select your cell or range of cells, IE A1:A72) On the ribbon click Conditional Formatting3) Click on New Rules, itâ€™s near the bottom of the dialog box.4) Click Use Formula to determine which cells to format.5) Enter the formula: =AND(EXACT(MID(A1,LEN(A1)-5,2),MID(A1,LEN(A1)-3,2)),EXACT(MID(A1,LEN(A1)-3,2),RIGHT(A1,2)))6) Click on the Format button7) Select the Fill Tab8) Select a pretty color9) Click OK10) Click OKIF your data looks like:``` A 1) 9826-131313 2) 9826-131314 3) 9826-121215 4) 9826-161616 5) 9826-131317 6) 9826-131318 7) 9826-353535 ```Then rows 1, 4 & 7 should be highlighted.See how that works for you.Hopfully this what your after.MIKEhttp://www.skeptic.com/