Archive of Mr Excel Message Board


Back to Data in Excel archive index
Back to archive home



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.

rich


Re: fixed length character fields

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


Re: fixed length character fields

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

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.

Aladin

=======



Re: fixed length character fields

Posted by Rich on October 24, 2001 10:10 AM
Thanks a lot guys. It worked like a charm.




This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.