Formula for validating text in cell with pattern of numbers and letters

**sweetusmile** · 07-17-2012, 10:44 AM

Hi,

I am looking out for a excel formula that helps me to find out the cells that has first four characters as numbers and next two characters as letters. Eg - 1234AB. I have a list in column A that has only letters and only numbers and alphanumeric cells and a few cells in the above format of 1234AB etc. I want a formula that identify such cells out of the list. Can anyone help please?

**Special-K** · 07-17-2012, 12:52 PM

Try

=AND(LEFT(A1,4)>999,ISERR(RIGHT(A1,2)+0))*1

and copy down the column

Cells containing 1 conform to your "1234AB" format

**sweetusmile** · 07-18-2012, 04:44 AM

Special K -

Thanks for your formula. However, it does not give me the expected results. It shows all the cells that are only alphabets and only numbers along with the alphanumeric cell in the format 1234AB

I have attached my excel sheet where i have put a formula in column C to show me only the cells that has "_NEW" to it. I have used your formula in column B, but does not seem working. Can you please have a look at the excel and let me know the suitable formula to identify only the cells in the format 1234AB

Thanks