Let’s assume that you want to share a spreadsheet with your team members, but you do not wish to allow them to enter a value more than a specific number. In other words, you want to set a character limit so that you can limit users from entering a value that goes through the preset filter. While it is easy to show an error or alert message in Excel, in this guide we will set the character limit in all the common apps that you use to edit a spreadsheet.
How to set a Character limit in Excel
To add a character limit in Excel, follow these steps- To know more, keep reading. At first, you will have to open a spreadsheet in Excel and select a cell. Then, go to the Data tab and expand the Data Validation drop-down list. Here you will find an option called Data Validation. Click on it.
Once the window is opened, make sure that you are in the Settings tab. If so, expand the Allow drop-down list, and select Text length. Following that, select limitation criteria from the Data list.
Next, you will have to enter a character limit as per your Data selection. Once done, click on the OK button. From now onward, whenever you fail to comply with the limit in the specific cell, it will show you an error message.
How to add a character limit in Excel Online
To add a character limit in Excel Online, follow these steps- To get started, open a spreadsheet in Excel Online and switch to the Data tab. Here you will see an option called Data Validation. Click on it.
After opening the window, expand the Allow drop-down list, and select Text Length. Following that, select a requirement from the Data drop-down list. Next, you will have to enter the character limit in the Maximum and Minimum boxes.
At last, click on the OK button to save the change. For your information, you can show an Input Message and Error Alert. They appear whenever someone tries to enter a value or enter a wrong value in the selected cell. For that, switch to the Input Message or Error Alert tab, and enter the desired message that you want to show.
How to limit Characters in Google Sheets
To add a character limit in Google Sheets, follow these steps- Let’s check out the detailed version of these steps. At first, open a spreadsheet in Google Sheets and select a cell where you want to add the filter. Following that, click on the Data option in the top menu bar and choose the Data validation option from the list. Once it is opened, select a Cell range. If it is one cell, nothing is mandatory to change. Then, expand the Criteria drop-down list, and choose the Custom formula is option. Next, you will have to enter the following formula in the corresponding box-
Before going to the next step, it is required to change two things according to your requirement. First, A1 represents the cell number. Second, 1,6 defines the character limit. If you set the same thing as mentioned here, it will do the following- users cannot add any value more than 6 in the A1 cell. Like Excel and Excel Online, you can show a warning or information text. For that, check the Show validation help text box, and enter a text. Apart from that, you can reject the value directly. For that, select Reject input instead of the Show warning option. At last, click the Save button to save the change. That’s all! Hope it helps.
