Format Cell

[Rita Hill]

How do I format a cell to hold (only) 3 letters and 5 digits? Seems to me
this should work, but it does not.
@@@#####

Thank you tremendously!

Rita

 

[Nalani]

Welcome to the Board, Rita

I think maybe more info would help.

1. What version of Excel
2. What range of cells
3. How exactly are you inputting. (typing in just 3 letters and 5 numbers, or more)
4. Depending on what is in the cell(s) and how they got there, there are functions that can strip out the unwanted characters leaving you with just what you want.

If you could post a few rows of your data, that would help in comming up with a solution. And please try to answer the other questions.

Formating may not be the option, but there are others options.

 

[T. Valko]

How do I format a cell to hold (only) 3 letters and 5 digits? Seems to me
this should work, but it does not.
@@@#####

Thank you tremendously!

Rita

It sounds like you want to use data validation.

What version of Excel are you using?

 

[jeffreybrown]

Hi Biff,

The OP is using 2007

http://www.mrexcel.com/forum/showthread.php?t=550122

 

[T. Valko]

It sounds like you want to use data validation.

What version of Excel are you using?

Hi Biff,

The OP is using 2007

http://www.mrexcel.com/forum/showthread.php?t=550122

Ok, create these defined names:

Goto the Formulas Tab>Defined Names>Define Name

• Name: Array1
• Refers to: ={1,2,3}
• Name: Array2
• Refers to: ={4,5,6,7,8}
• Name: Letters
• Refers to: ="ABCDEFGHIJKLMNOPQRSTUVWXYZ"

Let's assume you want to apply this to cell A1.

• Select cell A1
• Goto the Data tab>Data Validation
• Allow: Custom
• Formula: =AND(LEN(A1)=8,FIND(MID(A1,Array1,1),Letters),ISNUMBER(-MID(A1,Array2,1)))
• Uncheck: Ignore Blank
• OK out

Cell A1 will only accept entries where:

• the total length must be 8 characters
• the first 3 characters must be uppercase letters A to Z
• the next 5 characters must be digits 0 to 9

 

[grizz]

assuming cell A1 then use data / validation / custom / formula

=AND(LEN(A1)=8,ISTEXT(LEFT(A1,3)),ISNUMBER(--RIGHT(A1,5)))=TRUE

 

[T. Valko]

assuming cell A1 then use data / validation / custom / formula

Quote:
<TABLE cellSpacing=0 cellPadding=6 width="100%" border=0><TBODY><TR><TD class=alt2 style="BORDER-RIGHT: 1px inset; BORDER-TOP: 1px inset; BORDER-LEFT: 1px inset; BORDER-BOTTOM: 1px inset">=AND(LEN(A1)=8,ISTEXT(LEFT(A1,3)),ISNUMBER(--RIGHT(A1,5)))=TRUE </TD></TR></TBODY></TABLE>

I don't think that'll work.

It will allow entries like:

:::123.4

 

[Marcelo Branco]

Ok, create these defined names:



Goto the Formulas Tab>Defined Names>Define Name

• Name: Array1
• Refers to: ={1,2,3}
• Name: Array2
• Refers to: ={4,5,6,7,8}
• Name: Letters
• Refers to: ="ABCDEFGHIJKLMNOPQRSTUVWXYZ"

Let's assume you want to apply this to cell A1.

• Select cell A1
• Goto the Data tab>Data Validation
• Allow: Custom
• Formula: =AND(LEN(A1)=8,FIND(MID(A1,Array1,1),Letters),ISNUMBER(-MID(A1,Array2,1)))
• Uncheck: Ignore Blank
• OK out

Cell A1 will only accept entries where:

• the total length must be 8 characters
• the first 3 characters must be uppercase letters A to Z
• the next 5 characters must be digits 0 to 9

Valko,

A suggestion: i think you can use ROW(1:3) instead of Array1 and ROW(4:8) instead of Array2

M.

 

[T. Valko]

Valko,

A suggestion: i think you can use ROW(1:3) instead of Array1 and ROW(4:8) instead of Array2

M.

Yeah, you could but using the array constants is more robust.

Let's assume you do use ROW(1:3).

If you happen to insert a new row 1 then ROW(1:3) changes to ROW(2:4) and now you're no longer checking the first character of the string.

If you don't want to create the named constants then you can use this:

ROW(INDIRECT("1:3"))

That is robust against row insertions.