MrExcel Consulting
Your One Stop for Excel Tips & Solutions

fixed length character fields

Posted by Rich on October 24, 2001 9:16 AM

Hi Everyone,

I was wondering if there was a way to create a fixed length character column in Excel.

I created an Excel template for users to input data in different columns that would be imported into the DB.

the problem is that some of the DB fields are acertain length.

Is there a way to restrict the data input from a user to only a certain number of characters in a column? For example, if the user keyed in "Hello" and the fixed length was only 2 char, Excel would only accept "He"

I've tried formatting and Condition Formatting the columns but to no avail. Perhaps I'm approaching this the wrong way.

Any suggestions would be greatly appreciated.


Posted by Juan Pablo on October 24, 2001 9:27 AM

If you use Data Validation, you can choose one option that is Length of text, and Excel won't accept anything that differs from that length.

Juan Pablo

Posted by Aladin Akyurek on October 24, 2001 9:28 AM


You could use the data validation feature for this.

Select all cells where you expect input. Lets say that this range A1:D20.

Activate Data|Validation.
Select Custom for Allow in the Data Validation window.
Enter as formula, for example,

=LEN(A1) < 9<p>Design also an appropriate message on the last tab if you so desire.

Click OK.

The above will warn the user if it inputs anything longer than 8 chars.



Posted by Rich on October 24, 2001 10:10 AM

Thanks a lot guys. It worked like a charm.