how to display left characters number when a cell length is fixed in excel

Question

Hi there,

is there anyway to display the left character number when a cell has a fixed length requirement. the Conditional Format has different ways to point out visually if a length condition is met. But isn't it better to tell in advance how many characters left when typing is in progress.

thanks a lot,

Sheldon

Answer 1

Hi,

Yes, it is possible and definitely better. Here's how you can do it-

1. Choose the cell or cells where the character limit should be applied.
2. Go to the "Data" tab in the Excel ribbon.
3. Select "Data Validation" from the "Data Tools" group.
4. Navigate to the "Settings" tab in the "Data Validation" dialog box.
5. Go to the "Allow" dropdown and select "Text Length."
6. Pick the option that best suits your needs from the "Data" section, like If you want to specify a range, use "between", "greater than" if you want a minimum character limit, "less than" if you want a maximum character limit.
7. In the "Minimum" and/or "Maximum" boxes, enter the character limits you want.
8. Go to the "Input Message" tab in the same dialog box.
9. The "Show input message when cell is selected" checkbox should be selected.
10. Enter a title and an input message. In the input message, you can include a dynamic reference to the remaining characters using a formula like this - =100-LEN(A1) [CODE]
11. To implement the data validation, click "OK".

Best Regards.

Answer 2

Hi Tanay,

thank you very much! the only issue right now is your formula, =100-Len(a1), is displayed literarily in the Input Message, instead of calculated dynamically. Could you elaborate a little more on how to merge the result of a formula inside the Input Message?

thanks,

Sheldon

Answer 3

Hello! I believe you are looking for a way to display the number of characters left when typing in a cell with a fixed length requirement in Excel. There are a couple of ways to achieve this.

One way is to use a custom number format to display leading zeros. You can create a custom format with the same number of zeros (0) as digits that you want to display. For example, if you want to display a fixed-length number with five digits, create a custom number format with five zeros. This will display the leading zeros and the typed numbers in the cell ¹.

Another way is to use the LEFT function in Excel. The LEFT function extracts a specified number of characters from the beginning of a text string. You can use this function to extract the first few characters from the cell and then subtract it from the fixed length requirement to get the number of characters left. For example, if you have a cell with a fixed length requirement of 10 characters and you want to display the number of characters left when typing, you can use the formula =10-LEN(A1) where A1 is the cell reference ²³.

I hope this helps! Let me know if you have any other questions.