Limiting text entries

[Help4me]

I have two columns in which I would like to limit the way text is input:

1) Lastname,First initial with no spaces (i.e., Smith,R)
2) One capital letter followed by five digits (i.e., D12345)

I do not have any ideas for column 1.

I can use the Limited Text validation feature for column 2, but that only
limits the number of characters, not the type of characters input.

Thank you so much.

 

[Joerg Mochikun]

As a start for column 1:

For A1 apply the custom validation formula
=MID(A1,LEN(A1)-1,1)=","

This will allow only entries where the second from last character is a
comma.
You can use the AND function to combine more criteria in your formula and
make it as sophisticated as you like.

Joerg

 

[Joerg Mochikun]

And here is the formula for column 2:
=AND(LEN(B1)=6,ISNUMBER(RIGHT(B1,5)*1),ISTEXT(LEFT(B1,1)),CODE(B1)<91)

Four statements in the AND function, separated by commas check if
1) Entry is 6 characters long
2) Last 5 characters evaluate as number (has to be multiplied by 1,
otherwise Excel regards it as text)
3) Left character is text
4) Left character is uppercase (ASCII < 92)

Cheers,

Joerg Mochikun

 

[Joerg Mochikun]

ASCII <91 (formula is correct)

And here is the formula for column 2:
=AND(LEN(B1)=6,ISNUMBER(RIGHT(B1,5)*1),ISTEXT(LEFT(B1,1)),CODE(B1)<91)

Four statements in the AND function, separated by commas check if
1) Entry is 6 characters long
2) Last 5 characters evaluate as number (has to be multiplied by 1,
otherwise Excel regards it as text)
3) Left character is text
4) Left character is uppercase (ASCII < 92)

Cheers,

Joerg Mochikun

 

[sandypeter111]

i thing some formula is available, this method apply on that cell then
solve your query.....

 

[Help4me]

This formula looks great, but it did not work for me. What did I do wrong?
In data validation in cell B1, I selected "Custom" and then pasted your
formula. When I typed in "B12345" in cell B1 and hit tab, I received an
error message: "The value you entered is not valid. A user has restricted
values that can be entered into this cell."

 

[Help4me]

In data validation for cell A1, I selected "Custom" and then pasted your
formula. Unfortunately, it did not work and I got the validation error
message. Did I do something wrong?

 

[Help4me]

Please disregard my last posts. My spreadsheet info. starts on row 2 and I
had forgotten to change the cell references to indicate that. Both formulas
appear to be working beautifully, thank you SO much. However, I am getting a
circular reference for both formulas. I am hoping that will not mess with
any results.

Thanks again!