如果您想在 Excel 和 Google 表格中添加字符限制(add a character limit in Excel and Google Sheets),那么本文将对您有所帮助。您还可以显示错误或警报消息。无论您使用的是Word桌面应用程序还是Excel Online,您都可以在本教程中执行相同的操作。
假设您想与团队成员共享电子表格,但不希望他们输入超过特定数字的值。换句话说,您希望设置字符限制,以便您可以限制用户输入通过预设过滤器的值。虽然在 Excel 中显示错误或警告消息(show an error or alert message in Excel)很容易,但在本指南中,我们将在您用于编辑电子表格的所有常用应用程序中设置字符限制。
如何在Excel中设置(Excel)字符(Character)限制
要在Excel(Excel)中添加字符限制,请按照下列步骤操作 -
- 打开电子表格并选择一个单元格。
- 转到数据(Data)选项卡。
- 从数据验证(Data Validation)下拉列表中选择数据验证。(Data Validation)
- 从允许(Allow)下拉列表中展开文本长度。(Text length)
- 从数据(Data)列表中选择条件。
- 在“最小值(Minimum)”和“最大值(Maximum)”框中输入文本长度。
- 单击确定(OK)按钮。
要了解更多信息,请继续阅读。
首先,您必须在Excel中打开一个电子表格并选择一个单元格。然后,转到 数据 (Data )选项卡并展开 数据验证 (Data Validation )下拉列表。在这里,您将找到一个名为 Data Validation的选项。点击它。
打开窗口后,请确保您位于“ 设置” (Settings )选项卡中。如果是这样,请展开 允许 (Allow )下拉列表,然后选择 文本长度(Text length)。然后,从数据 (Data )列表中选择限制条件 。
接下来,您必须根据您的数据(Data)选择输入字符限制。完成后,单击“ 确定 (OK )”按钮。
从现在开始,每当您未能遵守特定单元格中的限制时,它都会向您显示错误消息。
如何在Excel Online中添加字符限制(Excel Online)
要在Excel Online(Excel Online)中添加字符限制,请按照以下步骤操作 -
- 在 Excel Online 中打开电子表格。
- 切换到数据(Data)选项卡。
- 单击数据验证(Data Validation)选项。
- 从允许(Allow)列表中选择文本长度。(Text Length)
- 从数据(Data)列表中选择一个字符限制。
- 在“最小值(Minimum)”和“最大值(Maximum)”框中输入限制。
- 单击确定(OK)按钮。
要开始使用,请在Excel Online中打开一个电子表格并切换到“ 数据 (Data )”选项卡。在这里,您将看到一个名为 Data Validation的选项。点击它。
打开窗口后,展开 允许 (Allow )下拉列表,然后选择 文本长度(Text Length)。然后,从数据 (Data )下拉列表中选择一个需求 。接下来,您必须在“最大值(Maximum)”和“最小值(Minimum)”框中输入字符限制。
最后,单击 OK 按钮保存更改。为了您的信息,您可以显示输入消息(Input Message)和错误警报(Error Alert)。每当有人尝试在所选单元格中输入值或输入错误值时,它们就会出现。为此,请切换到 输入消息 (Input Message )或 错误警报 (Error Alert )选项卡,然后输入要显示的所需消息。
如何限制Google表格中的字符
要在Google 表格(Google Sheets)中添加字符限制,请按照以下步骤操作 -
- 在Google 表格(Google Sheets)中打开电子表格。
- 选择一个单元格。
- 单击菜单栏中的数据(Data)。
- 从列表中选择数据验证。(Data validation)
- 选择单元格范围(Cell range)。
- 展开Criteria下拉列表并选择Custom formula is option。
- Enter =regexmatch (A3&””,”^(.){1,5}$”)。
- 选择显示警告(Show warning)选项。
- 单击保存(Save)按钮。
让我们看看这些步骤的详细版本。
首先,在Google 表格(Google Sheets)中打开一个电子表格,然后选择一个要添加过滤器的单元格。之后,单击 顶部菜单栏中的数据选项,然后从列表中选择 数据 (Data )验证选项。 (Data validation )
打开后,选择一个 单元格范围(Cell range)。如果是一个单元格,则无需更改任何内容。然后,展开 Criteria 下拉列表,并选择 Custom formula is 选项。
接下来,您必须在相应的框中输入以下公式 -
=regexmatch(A1&"","^(.){1,6}$")
在进行下一步之前,需要根据您的要求更改两件事。首先(First), A1 代表细胞数。其次(Second), 1,6 定义了字符限制。如果您设置与此处提到的相同的内容,它将执行以下操作 - 用户不能在 A1 单元格中添加任何超过 6 的值。
与Excel和Excel Online一样,您可以显示警告或信息文本。为此,请选中 显示验证帮助文本 (Show validation help text )框,然后输入文本。除此之外,您可以直接拒绝该值。为此,请选择 拒绝输入 (Reject input )而不是 显示警告(Show warning) 选项。
最后,单击“ 保存 (Save )”按钮保存更改。
就这样!希望能帮助到你。
How to set a Character limit in an Excel and Google Sheets
If you want to add a character limit in Excel and Google Sheets, then this article will help you. You can also show an error or alert message as well. Whether you are using the Word desktop app or Excel Online, you can do the same with this tutorial.
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-
- Open a spreadsheet and select a cell.
- Go to the Data tab.
- Select Data Validation from the Data Validation drop-down list.
- Expand Text length from Allow drop-down list.
- Select the criteria from the Data list.
- Enter a text length in the Minimum and Maximum boxes.
- Click on the OK button.
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-
- Open a spreadsheet in Excel Online.
- Switch to the Data tab.
- Click on the Data Validation option.
- Select Text Length from the Allow list.
- Select a character limit from the Data list.
- Enter the limit in the Minimum and Maximum boxes.
- Click on the OK button.
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-
- Open a spreadsheet in Google Sheets.
- Select a cell.
- Click on Data in the menu bar.
- Select Data validation from the list.
- Select the Cell range.
- Expand Criteria drop-down list and select Custom formula is option.
- Enter =regexmatch(A3&””,”^(.){1,5}$”) in the following box.
- Select the Show warning option.
- Click on the Save button.
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-
=regexmatch(A1&"","^(.){1,6}$")
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.