[Solved] Set character limit for cell

[evets]

Hello,
Sort of a newbie type question:
Trying to limit the number of characters in a cell (let's say 50 characters)
I click on the cell / validity / text length / data less than "50"). Close the dialog (Don't really care about warnings).

I go to the cell in question and start typing and there is no limit to the characters.
Am I missing something obvious.
Thanks in advance.
o/w - Love OO (long time user).

[RusselB]

Welcome to the Forums.
I believe that the limit controls how many characters are retained, not the number entered.
Thus if you go with your settings and type in 60 characters, only the first 50 are retained.
Did you hit the Enter key or move the cursor to a different cell? If you did. then your settings should've truncated the text at the 50 character mark.

[keme]

The validity check in Calc does not limit the size of data a cell can hold. It checks future input, and applies an action (specified in the "error alert" tab) when you try to enter your data.

• The default action is "Stop", which will display a message and discard the entire input. You will have to select the OK button in the message box, and reenter cell data in its entirety.
• A sensible action in many cases is "Warning" which will allow OK to accept the entry anyway, so you can subsequently edit it (or simply disregard the message). In this case the Cancel button will discard the input (and keep previous content in the cell, if any was present).
• To strip content at the desired length, you need the validation to trigger a macro which manipulates the input string.

Data entry form fields and Base table fields can have a max character setting, which I believe will behave more like what you request. Calc (like other spreadsheet apps) is more of a free-form data prototyping/modeling tool. It provides a very limited set of tools to ensure data integrity.

If you provide more detail about what you want to achieve (not just "what" and "how", but more of "why" and "in what context"), you may receive better advice (workarounds for your approach, different approaches towards your goal). Remember that this is a public place, so avoid sharing confidential information.

[evets]

for conversation's sake. I export a .csv data fields (from Filemaker Pro) in particular, one field often exceeds i.e. 50 characters, which is the maximum that my recipient will accept). Currently, I open the .csv and insert the "sheet from a file". I am disappointed to learn that the date in the column (cell) can't be easily truncated to meet the <50 criteria. I understand (now) that the warning allows me to go back and edit each egregious cell, but this would be cumbersome due to the number of records. Any thoughts / suggestions will be greatly appreciated!

[robleyd]

Use a helper cell with a formula to strip excess characters? For example LEFT("Text"; 50)

[keme]

Elaborating on the given solution.

For csv, the optimal workflow may be a spreadsheet file with two sheets, one "import" where you load your csv data, and one "export" with the formula given by robleyd. Right click sheet tabs to rename.

In cell A1 of the export sheet, Enter robleyd's formula, which for your purpose translates to this:

Code: Select all

=LEFT(Import.A1;50)

Copy the formula across the grid for as many rows and columns as you need.

Now, when you load the csv data in the import sheet, you can save from the export sheet and you will have long texts truncated.

[ludwighesse]

for conversation's sake. I export a .csv data fields (from Filemaker Pro) in particular, one field often exceeds i.e. 50 characters, which is the maximum that my recipient will accept). Currently, I open the .csv and insert the "sheet from a file". I am disappointed to learn that the date in the column (cell) can't be easily truncated to meet the <50 criteria. I understand (now) that the warning allows me to go back and edit each egregious cell, but this would be cumbersome due to the number of records. Any thoughts / suggestions will be greatly appreciated!

There have already been updates in FileMaker Pro, but I also have problems of a similar nature.

=LEFT(Import.A1;50)

This doesn't work.

[MrProgrammer]

There have already been updates in FileMaker Pro, but I also have problems of a similar nature. :crazy:

This topic is solved. If you have the same problem you can use the same solution (a helper cell). If you have a different problem, you should open your own topic so we can help with that. Do not post in a topic belonging to someone else unless you are providing a solution for their problem. Topic locked.

This doesn't work.

"It doesn't work" isn't helpful in the forum because it tells us what did not happen. Please never use that phrase in a post. We need to know exactly what actions you took, what did happen, and what you expected to happen. Attaching a document demonstrating the difficulty (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture) is almost always helpful for us and will get your problem solved more quickly. For example, if you typed a formula, pressed Enter, and got #VALUE!, tell us that, not "It doesn't work." Then attach your document so we can see what's wrong. Often "it didn't work" means "it didn't do what I expected" and the real problem is that the program is behaving correctly but your expectations are incorrect. To help you solve the problem we need to know your expectations and your ultimate goal.