# Find data (cell) with specific letter and number combination

• 05-15-2019, 03:36 AM
eXtremer
Find data (cell) with specific letter and number combination
Hi all,

I need to find the cells that have specific letter and number combination. So the cell must have: First letter, then 3 numbers and at the end another 2 letter. Can someone help me with this one?

This are the number and letter combination I'm looking for:

T773MK
T676EP
A947EH

Excel file attached.

Thank you.
• 05-15-2019, 03:54 AM
kersplash
Re: Find data (cell) with specific letter and number combination
You could apply this formula as a conditional format to the A:A column and then filter on the coloured cells;
Formula:
`=AND(ISERR(RIGHT(\$A1,2)*1),ISNUMBER(VALUE(MID(\$A1,2,3))),ISERR(LEFT(\$A1,1)*1),LEN(\$A1)=6)`
• 05-15-2019, 04:11 AM
torachan
Re: Find data (cell) with specific letter and number combination
Try the attached.
Enter in box, press search, should then scroll to row.
torachan.
• 05-15-2019, 07:25 AM
eXtremer
Re: Find data (cell) with specific letter and number combination
Quote:

Originally Posted by kersplash
You could apply this formula as a conditional format to the A:A column and then filter on the coloured cells;
Formula:
`=AND(ISERR(RIGHT(\$A1,2)*1),ISNUMBER(VALUE(MID(\$A1,2,3))),ISERR(LEFT(\$A1,1)*1),LEN(\$A1)=6)`

Thank you, but I get an error when trying to apply the code:
Attachment 624500

Quote:

Originally Posted by torachan
Try the attached.
Enter in box, press search, should then scroll to row.
torachan.

What should I do with the search box? Sorry, I cannot understand.
• 05-15-2019, 07:37 AM
torachan
Re: Find data (cell) with specific letter and number combination
Type in the box to the left of the 'SEARCH' button what you are searching for (e.g. T773MK)
Ensure the entry is precise, i.e. Uppercase/Lower case correct, space/nospaces etc.
Press 'SEARCH' !bingo!, if it cannot find anything you will get a message box saying so.
torachan.
• 05-15-2019, 08:18 AM
eXtremer
Re: Find data (cell) with specific letter and number combination
Quote:

Originally Posted by torachan
Type in the box to the left of the 'SEARCH' button what you are searching for (e.g. T773MK)
Ensure the entry is precise, i.e. Uppercase/Lower case correct, space/nospaces etc.
Press 'SEARCH' !bingo!, if it cannot find anything you will get a message box saying so.
torachan.

Thank you for your help bu I think you didn't understand my question. I need to find all the number and letter combination that has first letter then 3 numbers and at the end 2 letters.
• 05-15-2019, 08:19 AM
kersplash
Re: Find data (cell) with specific letter and number combination
My option is a conditional format applied to Column A, not a formula.
• 05-15-2019, 09:02 AM
eXtremer
Re: Find data (cell) with specific letter and number combination
Quote:

Originally Posted by kersplash
My option is a conditional format applied to Column A, not a formula.

Did you see the error in the attachment?
• 05-15-2019, 10:37 AM
kersplash
Re: Find data (cell) with specific letter and number combination
`=IF(AND(ISERR(LEFT(A2,1)*1),ISNUMBER(VALUE(MID(A2,2,3))),ISERR(RIGHT(A2,2)*1)),"Yes","No")`