Controlling a cells format - Alpha Numeric (x4) Alpha Alpha

[Dobbsylondon]

Good day all,

First post, never lurked.

I'm confident you guys are the ones to ask though.
In an existing sheet, I'd like to control the format of cells in a column. Namely restrict it to ANNNNAA (A=Text and N=numbers), 7 characters maximum as it's really meant to accept only existing unique ID numbers.

I've tried to use \####\\ or "",####"","" which may or may not obviously work as a custom. As my understanding is at it's limit, I thought it time to ask for some help.


Thank you all in advance and I hope to repay the favour at some point.

Kind regards

 

[Rick Rothstein]

Give this formula a try...

=AND(IF(MID("A0000AA",{1,2,3,4,5,6,7},1)="A",(MID(A1,{1,2,3,4,5,6,7},1)>="A")*(MID(A1,{1,2,3,4,5,6,7},1)<="Z"),ISNUMBER(-MID(A1,{1,2,3,4,5,6,7},1))))

Note1: The formula is a modification of one 'pgc01' posted here... http://www.mrexcel.com/forum/excel-questions/719296-check-string-cell.html#post3543996

Note2: The letters in the cell are assumed to always be entered in upper case.

There is a minor flaw with the above formula... it returns TRUE for entries that are longer than 7 characters so long as the first 7 characters meet the stated condition. This revision fixes that problem...

=IF(LEN(A1)=7,AND(IF(MID("A0000AA",{1,2,3,4,5,6,7},1)="A",(MID(A1,{1,2,3,4,5,6,7},1)>="A")*(MID(A1,{1,2,3,4,5,6,7},1)<="Z"),ISNUMBER(-MID(A1,{1,2,3,4,5,6,7},1)))))

As for a custom formula for use in Data Validation, this version of the formula seems to work...

=IF(LEN(A1)=7,AND(IF(MID("A0000AA",ROW(1:7),1)="A",(MID(A1,ROW(1:7),1)>="A")*(MID(A1,ROW(1:7),1)<="Z"),ISNUMBER(-MID(A1,ROW(1:7),1)))))

 

[István Hirsch]

Your formula will accept non-alphanumeric characters in the first, sixth and seventh positions; for example, it will return TRUE for "/1234-/" (without the quotes, of course).

Thanks, I see now. Then try this with 6 functions (MID*MID part was borrowed from post #11):

=SUM((MID(A1,{1,6,7},1)>="A")*(MID(A1,{1,6,7},1)<="Z"),ISNUMBER(1*MID(A1,{2,3,4,5},1))*{10,10,10,10})+100*(LEN(A1)=7)=143

 

[bobsan42]

Well,
I thought this should solve it.

=AND(LEN(A1)=7,CellControlMatchRegEx("[A-Za-z]{1}[0-9]{4}[A-Za-z]{2}",A1))

The code for the custom function

Function CellControlMatchRegEx(StrPattern As String, CellRng As Range) As Boolean

    Dim regEx As Object
    Set regEx = CreateObject("vbscript.regexp")

    regEx.Pattern = StrPattern
    regEx.IgnoreCase = True
    regEx.Global = True
    
    Dim IsSame As Object
    Set IsSame = regEx.Execute(CellRng.Value)

    If IsSame.Count > 0 Then CellControlMatchRegEx = True Else CellControlMatchRegEx = False

End Function

Unfortunately it turned out that excel doe not allow some things to be used in data validation amng them Custom functions (it accepts them as named ranges).

The workaround this i could think of is to use some of the suggested in the thread formulas (the one you like best) in another cell e.g. B1 - it will evaluate to eithe TRUE or FALSE.

Then for custom data validation on A1 you could use:

=B1=TRUE

guaranteed to work.
Hope this helps.

 

[Rick Rothstein]

Thanks, I see now. Then try this with 6 functions (MID*MID part was borrowed from post #11):

=SUM((MID(A1,{1,6,7},1)>="A")*(MID(A1,{1,6,7},1)<="Z"),ISNUMBER(1*MID(A1,{2,3,4,5},1))*{10,10,10,10})+100*(LEN(A1)=7)=143

This looks good... I can find no incorrect evaluations in my quick testing of it. Nice going, although I think we can simplify your formula a little bit by getting rid of the math an using a straight AND function call...

=AND((MID(A1,{1,6,7},1)>="A"),(MID(A1,{1,6,7},1)<="Z"),ISNUMBER(1*MID(A1,{2,3,4,5},1)),LEN(A1)=7)

 

[István Hirsch]

This looks good... I can find no incorrect evaluations in my quick testing of it. Nice going, although I think we can simplify your formula a little bit by getting rid of the math an using a straight AND function call...

=AND((MID(A1,{1,6,7},1)>="A"),(MID(A1,{1,6,7},1)<="Z"),ISNUMBER(1*MID(A1,{2,3,4,5},1)),LEN(A1)=7)

Wrapping in AND is a good idea. Thanks for streamlining the formula.

 

[Dobbsylondon]

Holy moley.

You guys are awesome. Thanks very much for this, you guys are life savers AND I may well have learned a little in the process.

Kindest regards


EDIT-

=AND((MID(A1,{1,6,7},1)>="A"),(MID(A1,{1,6,7},1)<="Z"),ISNUMBER(1*MID(A1,{2,3,4,5},1)),LEN(A1)=7)

I'm guessing this goes into the VBA editor. But i'm at a loss as to where it's inserted.

 

[bobsan42]

I'm guessing this goes into the VBA editor. But i'm at a loss as to where it's inserted.

No, it doesn't go in VBE - this is the formula to input in a cell in the worksheet.
The only VBA code in this thread is in (my) post #13.

But as I see it - all you need eventually is a combination of:
- the quoted formula (from post #14)
- and read the blue text in my post #13.
- no VBA - all done within the worksheet

 

[Rick Rothstein]

No, it doesn't go in VBE - this is the formula to input in a cell in the worksheet.
The only VBA code in this thread is in (my) post #13.

But as I see it - all you need eventually is a combination of:
- the quoted formula (from post #14)
- and read the blue text in my post #13.
- no VBA - all done within the worksheet

If the OP is really looking for a Data Validation formula, this one (which I posted in Message #11) seems to work correctly without needing any helper cells...

=IF(LEN(A1)=7,AND(IF(MID("A0000AA",ROW(1:7),1)="A",(MID(A1,ROW(1:7),1)>="A")*(MID(A1,ROW(1:7),1)<="Z"),ISNUMBER(-MID(A1,ROW(1:7),1)))))

 

[István Hirsch]

Holy moley.

You guys are awesome. Thanks very much for this, you guys are life savers AND I may well have learned a little in the process.

Kindest regards


EDIT-




I'm guessing this goes into the VBA editor. But i'm at a loss as to where it's inserted.

Not in the VBA editor; you should put the formula in the spreadsheet, in a column next to your data column. For example, if your data is in column A, starting from A1, then put the formula in B1 and copy down. Copy down means: after confirming the formula in B1 with Enter, place the mouse over the bottom right corner of cell B1 and when the fill handle (a plus symbol) appears, left click on it and drag it down as long as there is data in the data column. Alternatively, if there is not a blank cell in A, you can copy down the formula by clicking twice on the fill handle.

Excel Workbook
	A	B
1	B0000GT	TRUE
2	A00z0Af	FALSE
3	/1200A+	FALSE
4	0000Af	FALSE
5	A4567Af	TRUE
6	A4567Af3	FALSE
7	AT234Af	FALSE
Sheet

 

[Dobbsylondon]

If the OP is really looking for a Data Validation formula, this one (which I posted in Message #11) seems to work correctly without needing any helper cells...

=IF(LEN(A1)=7,AND(IF(MID("A0000AA",ROW(1:7),1)="A",(MID(A1,ROW(1:7),1)>="A")*(MID(A1,ROW(1:7),1)<="Z"),ISNUMBER(-MID(A1,ROW(1:7),1)))))

Would I change A1 to (in the case of my sheet) B:B to apply it to the whole B column?

Many thanks to all involved, and I really do appreciate every bit of advice.. Just to clarify. The sheet is already up and working on a networked drive and I just want to regulate - "guide" people who are forgetful in entering case numbers in the correct format. So am a bit hesitant to modify the structure by adding columns and changing existing layouts in case I nerf something already in place. Data validation has always seemed the least obtrusive way to me.