1 Attachment(s)

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.

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)

1 Attachment(s)

Re: Find data (cell) with specific letter and number combination

Try the attached.

Enter in box, press search, should then scroll to row.

torachan.

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.

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.

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.

Re: Find data (cell) with specific letter and number combination

My option is a conditional format applied to Column A, not a formula.

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?

Re: Find data (cell) with specific letter and number combination

Can't download the attachment (website error).

Re: Find data (cell) with specific letter and number combination

Hi,

You can try the following code:

Formula:

=IF(AND(ISERR(LEFT(A2,1)*1),ISNUMBER(VALUE(MID(A2,2,3))),ISERR(RIGHT(A2,2)*1)),"Yes","No")