ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

Limit Text Length in a Cell in Excel

Updated on March 22, 2011

You may have a requirement to limit the text length of cells in Excel. This could be to ensure that what is entered into the cell is exactly 9 characters, or is between 9 and 11 characters and so on.

Basic Validation of the Text Length

To limit the text length to a certain number of characters in Excel, we will use the Data Validation tool.

For this example, we would like to restrict the cell to exactly 9 characters to hold a Social Security Number or National Insurance Number.

1.  Select the column or range of cells you want to validate

2.  Click the Data tab on the Ribbon

3.  Click the Data Validation button in the Data Tools group

4.  Click the Allow: list arrow and select Text length

5.  Click the Data: list arrow and select equal to

6.  Type 9 in the Length: field

Limit text length to exactly 9 characters
Limit text length to exactly 9 characters

7.  Click the Ok button

Excel limits the text length of the data in the selected cells to 9 characters exactly.

Limit Text Length using Formulas

By using formulas you can create more advanced text length validation.

For example, we would like to restrict the cell to exactly 9 characters, but also ensure that no text characters are entered. This matches the format used for Social Security Numbers.

1.  Select the column or range of cells you want to validate. In this example column A has been selected

2.  Click the Data tab on the Ribbon

3.  Click the Data Validation button in the Data Tools group

4.  Click the Allow: list arrow and select Custom

5.  Type =AND(LEN(A1)=9,ISNUMBER(A1)) in the Formula box

Using formulas when validating text length
Using formulas when validating text length

The AND function is used to allow us to test more than one condition. The LEN function is used to ensure that exactly 9 characters are entered and the ISNUMBER function is used to ensure that no alphabetic characters are entered.

6. Click the Ok button

By using formulas in your data validation you can create some complex criteria that entries need to meet. By reducing inaccuracies in the data entered, you can ensure that your reports are more accurate and your figures correct.

Useful Links

Microsoft Excel training at Lynda.com

Online Microsoft Excel tutorials from the most respected authors on the market. Become an Excel guru by checking out this fantastic site.

Data Validation list tips

Tips on using Data Validation to create drop down lists in your spreadsheets.

Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.

    Click to Rate This Article