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

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+FI 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"?

or

2 - Are you searching for

everynumber that has a sequence of 3 pairs e.g. 131313, 353535, 575757 basically trying to highlight them all at once?or

3 - 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.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

Not completely sure if this is what your after,

but here is aCondidional Formattingsolution

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:A7

2) On the ribbon clickConditional Formatting

3) Click onNew Rules, itâ€™s near the bottom of the dialog box.

4) ClickUse 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

Formatbutton

7) Select theFillTab

8) Select a pretty color

9) Click OK

10) Click OK

IF 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-353535Then rows 1, 4 & 7 should be highlighted.

See how that works for you.

Hopfully this what your after.MIKE

It can also be done with a macro, where you could enter the digits you were looking for, e.g. 13. That code could then highlight the cell that contained 131313 or take you directly to that cell or present the address of the cell or return some data associated with that number. The code could highlight every number that contains 3 pairs, like Mike's suggestion does or do something specific with each number. The possibilities are essentially endless.

That's why I said:

"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."

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History