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

or

2 - 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?

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


Report •

#2
April 24, 2018 at 15:22:44
✔ Best Answer
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 2007

1) Select your cell or range of cells, IE A1:A7
2) On the ribbon click Conditional Formatting
3) 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 button
7) Select the Fill Tab
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-353535

Then rows 1, 4 & 7 should be highlighted.


See how that works for you.
Hopfully this what your after.

MIKE

http://www.skeptic.com/


Report •

#3
April 24, 2018 at 15:51:34
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


Report •
Related Solutions


Ask Question