跳到主要內容

如何限制Excel單元格中的字元長度？

Excel 中的儲存格最多可容納 32,767 個字符，僅顯示 1,024 個字符，而全部 32,767 個字符在公式欄中可見。 但是，有時您可能需要將使用者輸入限制為儲存格中的一定數量的字符，例如將其限制為 10 個字元。 本教學將向您展示如何有效限制 Excel 儲存格中的字元數。

限制單元格中的字元數

本節介紹限制可輸入特定儲存格或儲存格範圍的字元數的步驟。

  1. 選擇要強制執行字元長度限制的儲存格範圍。
  2. 點擊 數據驗證 ，在 數據工具 下的組 數據 標籤。
  3. 在 數據驗證 對話方塊中，請設定驗證規則如下：
    1. 從 允許： 下拉菜單，選擇 文字長度.
    2. 從 日期： 在下拉清單中，根據您的要求選擇標準：
      1. 選擇 等於 精確字元數的選項，例如 10 個字元。
      2. 選擇 小於或等於 最大字元數限制，例如最多 10 個字元。
      3. 選擇 大於 超過特定計數，例如超過 10 個字元。
    3. 在 最大值/最低限度/長度： 在框框中，根據您的需求輸入所需的字元數限制。
  4. 點擊 OK.

現在，使用者將被限制在所選儲存格中的設定字元限制內輸入文字。

輕鬆防止在Excel中的單元格/選擇中鍵入特殊字符，數字或字母

使用Kutools for Excel的 防止打字 功能，您可以輕鬆限制 Excel 儲存格或選取中的字元類型。
A。 阻止輸入特殊字符，例如 *, !等等
B。 限制輸入特定字符，例如數字或某些字母；
C。 僅允許根據需要輸入指定字符，例如數字或字母。

設定輸入訊息以通知字元限制

在這裡，我們將解釋如何設定輸入訊息，在使用者開始輸入之前告知使用者字元限制，如下圖所示：

  1. 選擇要新增輸入訊息的儲存格。
  2. 點擊 數據驗證 ，在 數據工具 下的組 數據 標籤。
  3. 在 數據驗證 對話框中，選擇 輸入信息。 標籤，然後執行以下操作：
    1. Check the 選擇單元格時顯示輸入消息 選項。
    2. 輸入訊息標題和訊息內容。
  4. 點擊 OK.

現在，當您按一下配置了輸入訊息的儲存格時，將出現一個工具提示，顯示您先前輸入的訊息標題和內容。

針對違反字元限制啟動錯誤警報

本部分將引導您在 Excel 中配置錯誤警報，旨在在輸入的資料不符合指定的字元限制時立即通知使用者。 看截圖：

  1. 選擇要針對無效資料輸入實例啟動錯誤警報的儲存格。
  2. 點擊 數據驗證 ，在 數據工具 下的組 數據 標籤。
  3. 在 數據驗證 對話框中，選擇 錯誤警報 標籤，然後執行以下操作：
    1. Check the 輸入無效數據後顯示錯誤警報 選項。
    2. 點擊 警告 選項從 風格： 下拉框。
    3. 輸入警報標題和錯誤訊息。
  4. 點擊 OK.

從現在開始，如果在儲存格中輸入的文字無效，例如超過10個字符，則會出現警告對話框，顯示預設的警報標題和訊息。

演示：透過輸入訊息和警報警告限制單元格中的字元長度


Excel的Kutools：超過 300 個方便的工具觸手可及！ 立即開始 30 天免費試用，沒有任何功能限制。 現在就下載！

一鍵防止在單個列/列表中輸入重複數據

Kutools for Excel 的創新 防止重複 實用程式為 Excel 使用者提供了輕鬆防止清單或列中出現重複條目的便利性。 只需單擊一下，這個強大的工具即可確保電子表格中資料的完整性和準確性。

相關文章：

如何限制Excel中的單元格值條目？

最佳辦公生產力工具

🤖 Kutools 人工智慧助手：基於以下內容徹底改變數據分析： 智慧執行   |  生成代碼  |  建立自訂公式  |  分析數據並產生圖表  |  呼叫 Kutools 函數...
熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup  |   多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧   |  工作簿和工作表管理器   |  資源庫 （自動文字）   |  日期選擇器   |  合併工作表   |  加密/解密單元格   |  按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 （過濾粗體/斜體/刪除線...）...
前 15 個工具集:  12 文本 工具 (添加文本, 刪除字符，...）   |   50+ 圖表 類型 (甘特圖，...）   |   40+ 實用 公式 (根據生日計算年齡，...）   |   19 插入 工具 (插入二維碼, 從路徑插入圖片，...）   |   12 轉化 工具 (數字到單詞, 貨幣兌換，...）   |   7 合併與拆分 工具 (高級合併行, 分裂細胞，...）   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能，體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

產品描述

Office選項卡為Office帶來了選項卡式界面，使您的工作更加輕鬆

  • 在Word，Excel，PowerPoint中啟用選項卡式編輯和閱讀，發布者，Access，Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%，每天為您減少數百次鼠標點擊！
Comments (54)
No ratings yet. Be the first to rate!
Olena
Olena
This comment was minimized by the moderator on the site
Thank you!
Reply
0
0
Kyaw Kyaw
Kyaw Kyaw
This comment was minimized by the moderator on the site
why date and number is allowed at text length?
Reply
0
0
Amanda Lee
Amanda Lee     Kyaw Kyaw
This comment was minimized by the moderator on the site
Hi there, numbers are also characters. So if you set the max length less than 10 characters, as long as you don't input 10 or more number characters, you will be good.

Amanda
Reply
Report
0
0
JJ
JJ
This comment was minimized by the moderator on the site
This is almost the exact solution I need but still need a bit more help. What I'm trying to achieve is to set a cell to have a max of 40 characters but not stop the user from entering all the data he needs, instead i would like anything over the 40 character limit to be populated in a second designated cell. Is this even a possibility? Thank you everyone in advance for any assistance provided.
Reply
0
0
Guest
Guest     JJ
This comment was minimized by the moderator on the site
I found this information useful to resolve a similar issue that I am facing.

https://support.microsoft.com/en-us/office/split-text-into-different-columns-with-the-convert-text-to-columns-wizard-30b14928-5550-41f5-97ca-7a3e9c363ed7
Reply
0
0
Michael
Michael
This comment was minimized by the moderator on the site
I saw Tomas question about putting an exact limit of 10 spaces and your formula =A1&REPT(" ",10-LEN(A1)) however I need to take it a step further. I want to take three separate fields that I have set their spaces to exactly 10 and concatenate them with their "spaces" intact. Also, if I don't limit what they enter and they enter MORE than 10 characters in one of the cells I want to take ONLY the first 10. So as an example, I want to give them three cells they can enter information in. I don't want to limit what they input BUT, I want to concatenate these three fields and pick up 10 characters from each one. So if the first cell has 4 characters, in my concatenate formula I want it to pick up the 4 characters PLUS 6 spaces. If the second field has 20 characters, I want to only pick up the first 10 characters and the same thing for the third cell. We are trying to get to a uniform 30 characters of description but some names are longer or shorter than others. We want this to break evenly with 10 characters per cell. Hopefully this is making sense.
Reply
0
0
Tomas
Tomas
This comment was minimized by the moderator on the site
Hi, do you know how to put exact length limit 10 and when put abc i want from excel to put 8 space?
I want to set a cell to 10 character, when input 2 character then will auto fill up with 8 space after. if the cell is blank, then return with 10 space. this is for setting a excel file for user input and save as txt or cvs file for import to other software.
Reply
0
0
kellytte
kellytte     Tomas
This comment was minimized by the moderator on the site
Hi Tomas,
You can use a formula to limit the text length: =A1 & REPT(" ",10-LEN(A1))
Reply
Report
0
0
question
question
This comment was minimized by the moderator on the site
I want to limit the quantity of a cell depending on the category of another one,

For example if I input in A1 "Ok" in B1 must be limit to 10 characters
but if A1= "NG", B1 must be limit to 12 characters.
Reply
0
0
Eliza
Eliza
This comment was minimized by the moderator on the site
[quote]Hi, do you know how to put exact length limit 10 and when put abc i want from excel to put 8 space?By Ivan[/quote] I want to set a cell to 10 character, when input 2 character then will auto fill up with 8 space after. if the cell is blank, then return with 10 space. this is for setting a excel file for user input and save as txt or cvs file for import to other software
Reply
0
0
ARNAB DEBNATH
ARNAB DEBNATH
This comment was minimized by the moderator on the site
Hi, I have a attendance sheet. from 1 to 31. I put "P" on each cell if person is present. Now I want that how many times "P" is continuing present in cell. As as example - I have put "P" from 1 to 6 , then from 8th to 9th put P, and 10th is gap. then from 11th its continue to 18th. ... now i want how many times P is continue 6 time . PPPPPP PP PPPPPPPP Manually the answer is : 2(1to6 = 1,11to18=1) If you have any formula to count this it will be a great help.
Reply
0
0
Paul
Paul     ARNAB DEBNATH
This comment was minimized by the moderator on the site
=COUNTIF(B2:B17,">""") this formula will ignore empty cells but will count cells with data in i.e. P
Reply
0
0
Shawn
Shawn
This comment was minimized by the moderator on the site
Hi. I want out put txt file and no spaces between cell values. Like 3 cells with First Name, Middle and last. Entered Shawn G Goldman as SHAWNGGOLDMAN
Reply
0
0
Shawn
Shawn
This comment was minimized by the moderator on the site
Hi I want few things in a cell. I only want numbers in cell. I want to limit to 10 characters. I want to remove decimal like 15.00 to 1500. I want to indent to right. Also to add 0's to left to make it 10 characters .like 15.00 to 0000001500
Reply
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations