Share via

Data Validation for mixed numeric and text

Anonymous
2015-10-07T09:24:28+00:00

Hi,

Is it possible to validate a excel cell with both numbers and text.

In Column A, I have a 14 character string of 2 letters, four numbers, two letters and six numbers. Like this:

AA1010DA016148.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2015-10-07T09:59:17+00:00

Hi,

Yes we can do that with a formula. Select A2 then drag to select as many cells in column A as you want to validate. Then:-

Data tab | Data validation | Data Validation | In the 'Allow' dropdown select 'Custom' and paste this formula in:-

=AND(LEN(A2)=14,ISNUMBER(-MID(A2,ROW(INDIRECT("3:6")),1)),FIND(MID(A2,ROW(INDIRECT("1:2")),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ"),FIND(MID(A2,ROW(INDIRECT("7:8")),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ"),ISNUMBER(-MID(A2,ROW(INDIRECT("9:14")),1)))

Ok out. As written the letters in your string must be upper case but if you want to allow mixed case then change the FIND in the formula (2 places) to SEARCH

Was this answer helpful?

1 person found this answer helpful.
0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2015-10-07T11:16:51+00:00

    Glad that worked for you, you might now like to mark my response as answer.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2015-10-07T10:25:25+00:00

    It is what I need and it works. Thank you very much Mike

    Was this answer helpful?

    0 comments