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.

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



"Help4me" wrote in message
...
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.

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" wrote in message
...
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



"Help4me" wrote in message
...
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.

ASCII <91 (formula is correct)

"Joerg Mochikun" wrote in message
...
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" wrote in message
...
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



"Help4me" wrote in message
...
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.

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




--
sandypeter111

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."

"Joerg Mochikun" wrote:

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" wrote in message
...
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



"Help4me" wrote in message
...
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.

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?

"Joerg Mochikun" wrote:

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



"Help4me" wrote in message
...
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.

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!

"Joerg Mochikun" wrote:

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



"Help4me" wrote in message
...
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.