Note: The other languages of the website are Google-translated. Back to English
登入  \/ 
x
or
x
註冊  \/ 
x

or

刪除Excel單元格中的空格-前導空格,尾隨空格,多餘空格或所有空格

有時,當數據已從其他應用程序複製並粘貼到工作表中時,如果多餘的空間不自覺地與數據一起出現,則可能出問題了。 例如,當添加兩個包含前導空格的數字單元格時,它將返回結果為0,而不是正確的結果。 或者在比較兩個具有相同內容但其中一個包含前導或尾隨空格的單元格時得到錯誤的結果。 如何刪除這些空格? 本教程將為您介紹幾種方法。


從單元格中刪除多餘的空間,包括前導,尾隨和中間多餘的空間

假設您有一個包含前導,尾隨和多餘的中間空格的單元格的列列表。 為了從單元格中刪除所有這些多餘的空間,TRIM功能可以提供幫助。

=TRIM(TEXT)

選擇一個空白單元格,輸入 =TRIM(B3) 進入它,然後按 Enter 鍵。 然後拖動它 填充手柄 將該公式應用於其他單元格。 看截圖:
備註:B3是包含要刪除空格的文本的單元格。

現在,所有多餘的空格(包括前導,尾隨和多餘的中間空格)都將從指定的單元格中刪除。

備註:此操作需要在新列中完成。 刪除所有多餘的空格後,您需要通過按 按Ctrl + C,選擇原始數據范圍,然後右鍵單擊以選擇 價值觀粘貼選項 部分。

單擊幾下以刪除單元格中不需要的空間

隨著 刪除空間 實用程序,只需單擊幾下就可以幫助您不僅刪除 前導空格,尾隨空格,多餘空格 但也 所有空間 一個範圍,多個範圍甚至整個工作表,這將節省大量工作時間。
Kutools for Excel - 匯聚300多項強大的高級功能,專為1500+工作場景設計,解決80%的Excel問題。

下載並試用全功能免費試用 30 天

 

僅刪除單元格中的前導空格

在某些情況下,您可能只需要刪除前導空格,並將所有中間空格保留在單元格中,如下面的屏幕截圖所示。 本節介紹解決此問題的另一個公式和VBA代碼。

使用MID函數僅刪除前導空格

選擇一個空白單元格(這裡我選擇單元格D3),將以下公式複製到其中,然後按 Enter 鍵。 然後拖動 填充手柄 將該公式應用於其他單元格。

=MID(B3,FIND(MID(TRIM(B3),1,1),B3),LEN(B3))

在這個公式中, B3 是包含要刪除前導空格的文本的單元格。

備註:此操作需要在新列中完成。 刪除所有前導空格後,您需要使用不帶公式的修剪後的數據替換原始數據。

使用VBA代碼僅刪除前導空格

1.打開包含要刪除前導空格的單元格的工作表,然後按 其他 + F11 鍵打開 Microsoft Visual Basic for Applications 窗口。

2。 點擊 插入 > 模塊,然後將以下VBA代碼複製到“模塊”窗口中。

Sub RemoveLeadingSpaces()
'Updateby20190612
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    Rng.Value = VBA.LTrim(Rng.Value)
Next
End Sub

3。 按 F5 鍵來運行代碼。 一種 Kutools for Excel 對話框彈出,請選擇要從中刪除前導空格的連續單元格,然後單擊 OK 按鈕。

現在,您只能看到從所選單元格中刪除了前導空格。


僅刪除單元格中的尾隨空格

1.打開包含要刪除前導空格的單元格的工作表,然後按 其他 + F11 鍵打開 Microsoft Visual Basic for Applications 窗口。

2。 點擊 插入 > 模塊,然後將以下VBA代碼複製到“模塊”窗口中。

Sub RemoveTrailingSpaces()
'Updateby20190612
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    Rng.Value = VBA.RTrim(Rng.Value)
Next
End Sub

3。 按 F5 鍵來運行代碼。 一種 Kutools for Excel 對話框彈出,請選擇要從中刪除尾隨空格的連續單元格,然後單擊 OK 按鈕。 現在,您只能看到從所選單元格中刪除了尾隨空格。


刪除單元格中的所有空格

如果要擺脫特定單元格中的所有空格,本節中的方法可能會對您有所幫助。

使用SUBSTITUTE函數刪除單元格中的所有空格

=SUBSTITUTE(text, old_text, new_text, [instance_num])

選擇一個空白單元格,將以下公式複製到其中,然後按 Enter 鍵。 然後拖動 填充手柄 將該公式應用於其他單元格。

=SUBSTITUTE(B3," ","")

保養竅門:在此公式中, B3 是包含要刪除所有空格的文本的單元格;
         “” (用雙引號引起的空格)表示要刪除的空格;
         "" 這意味著您將一無所有替換所有空格。

然後,您可以看到特定單元格中的所有空格都被立即刪除。

備註:此操作需要在新列中完成。 刪除所有空格後,您需要用沒有公式的修剪後的數據替換原始數據。

使用查找和替換功能刪除單元格中的所有空格

除了以上 替代 功能,“查找和替換”功能還可以幫助刪除單元格中的所有空格。

1.選擇包含要刪除的空格的單元格,然後按 按Ctrl + H 鍵打開 查找和替換 對話框。

2。 在裡面 查找和替換 對話框並在 更換 標籤,在其中輸入一個空格 查找內容 文本框,保留 更換 文本框為空,然後單擊 “全部替換” 按鈕。


使用Kutools輕鬆刪除單元格中的前導,尾隨,多餘和所有空格

小技巧: :如果您厭倦了使用公式和VBA代碼刪除空格, 刪除空間 的效用 Kutools for Excel 是您最好的選擇。 只需單擊幾下,您不僅可以刪除前導空格,尾隨空格,多餘空格,還可以刪除一個範圍,多個範圍甚至整個工作表中的所有空格,這將節省大量的工作時間。

在使用Kutools for Excel之前,您需要花費幾分鐘時間 免費下載並安裝 首先。

1.選擇一個或多個要刪除單元格的區域,單擊 庫工具 > 文本 > 刪除空間。 看截圖:

2.您可以看到其中有5個選項 刪除空間 對話框:

  • 要僅刪除前導空格,請選擇 前導空間 選項;
  • 要僅刪除尾隨空格,請選擇 尾隨空格 選項;
  • 要同時刪除前導空格和尾隨空格,請選擇 前導和尾隨空格 選項;
  • 要刪除所有多餘的空格(包括前導,尾隨,多餘的中間空格),請選擇 所有多餘的空間 選項;
  • 要刪除所有空格,請選擇 所有空間 選項。

然後點擊 OK 按鈕開始操作。

  如果您想免費試用此實用程序(30 天), 請點擊下載,然後按照上述步驟進行操作。


其他與Excel空間有關的實際案例

除了從單元格中刪除空格外,您是否遇到過計數,添加空格或用其他字符替換空格的情況? 以下建議可能會加快您的Excel工作。

計算一個單元格中的總空間數
在刪除一個單元格中的所有空格之前,您可能會對其中有多少個空格感到好奇。 本教程提供詳細步驟中的方法,以幫助您快速獲取單元格中的空間總數。
點擊了解更多...

在特定的Excel單元格中的每個逗號後添加空格
有時,空間可能會意外地從特定單元格中刪除。 本教程討論的是在每個逗號後添加一個空格,以使文本字符串更清晰,更標準,更詳細。
點擊了解更多...

在Excel單元格中的數字之間添加空格
本教程討論的是在Excel單元格的每個數字或第n個數字之間添加空格。 假設您有一列電話號碼,並且想要在它們之間添加空格以使該電話號碼看起來更直觀且更易於閱讀。 本教程中的方法會有所幫助。
點擊了解更多...

在Excel單元格中大寫字母前添加空格
本教程討論的是在Excel單元格中的每個大寫字母之前添加一個空格。 假設您有一個意外刪除所有空格的文本字符串列表,例如:InsertBlankRowsBetweenData,要在每個大寫字母之前添加空格以將單詞分隔為“在數據之間插入空白行”,請嘗試本教程中的方法。
點擊了解更多...

用Excel單元格中的特定字符替換空格
在許多情況下,您寧願用特定字符替換空格,而不是直接從單元格中刪除空格。 此處提供了一些方法,可以輕鬆用單元格中的下劃線,破折號或逗號替換空格。
點擊了解更多...



  • 超級公式欄 (輕鬆編輯多行文本和公式); 閱讀版式 (輕鬆讀取和編輯大量單元格); 粘貼到過濾範圍...
  • 合併單元格/行/列 和保存數據; 拆分單元格內容; 合併重複的行和總和/平均值...防止細胞重複; 比較範圍...
  • 選擇重複或唯一 行; 選擇空白行 (所有單元格都是空的); 超級查找和模糊查找 在許多工作簿中; 隨機選擇...
  • 確切的副本 多個單元格,無需更改公式參考; 自動創建參考 到多張紙; 插入項目符號,複選框等...
  • 收藏并快速插入公式,範圍,圖表和圖片; 加密單元 帶密碼 創建郵件列表 並發送電子郵件...
  • 提取文字,添加文本,按位置刪除, 刪除空間; 創建和打印分頁小計; 在單元格內容和註釋之間轉換...
  • 超級濾鏡 (將過濾方案保存並應用於其他工作表); 高級排序 按月/週/日,頻率及更多; 特殊過濾器 用粗體,斜體...
  • 結合工作簿和工作表; 根據關鍵列合併表; 將數據分割成多個工作表; 批量轉換xls,xlsx和PDF...
  • 數據透視表分組依據 週號,週幾等 顯示未鎖定的單元格 用不同的顏色 突出顯示具有公式/名稱的單元格...
kte選項卡201905
  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!
officetab底部
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Da345 · 2 years ago
    I removed all spaces with the Replace function, very easy, thank you
  • To post as a guest, your comment is unpublished.
    javad · 2 years ago
    thank you a lot

    it was very god
  • To post as a guest, your comment is unpublished.
    Rafik · 3 years ago
    Thank you a lot.
  • To post as a guest, your comment is unpublished.
    Ocaya · 4 years ago
    Excellent, Very helpful
  • To post as a guest, your comment is unpublished.
    alefpe · 4 years ago
    Thank you very much.
    It was so helpful.
  • To post as a guest, your comment is unpublished.
    alfonso · 4 years ago
    if it doesnt work for you. First you remove the spaces, then you remove the letters, for example: i have USD 1234.00 , first i do the find & replace just the space between USD and 1234.00, now i have USD1234.00, now i go back to find & replace and on find what: i put USD ( no spaces ), and nothing on REPLACE WITH: then i click on Replace all , and i have now 1234.00 if you do it the other way it doesnt work i dont know why.
  • To post as a guest, your comment is unpublished.
    ArvRajB · 4 years ago
    Thank you very much!!
  • To post as a guest, your comment is unpublished.
    Cecep Saefulloh · 4 years ago
    Great Tips especially for research keywords and to make a lot of hashtags on facebook, and You have great tools to make it simple

    It is appropriate that we visit each other and communicate
    Web Development Agency Konsultan Blog Teknologi
  • To post as a guest, your comment is unpublished.
    Joseph Wokwera · 4 years ago
    Thanks so much helpful. i ve been trying this for 2 days. it worked
  • To post as a guest, your comment is unpublished.
    PRAMOD · 4 years ago
    Excellent.Very helpful to remove space in a cell
  • To post as a guest, your comment is unpublished.
    PRAMOD · 4 years ago
    Excellent, nice formula to substitute or remove the spaces in a cell.
  • To post as a guest, your comment is unpublished.
    sachin · 4 years ago
    Thank You Very Much. help me a lot..
  • To post as a guest, your comment is unpublished.
    SUDHIR MISHRA · 5 years ago
    EXCELLENT :) THANK FOR HELP
  • To post as a guest, your comment is unpublished.
    Kosova · 5 years ago
    Thanks a lot, now its so easy.
  • To post as a guest, your comment is unpublished.
    RDM · 5 years ago
    THANK YOU! Really helped me and my bandwidth
  • To post as a guest, your comment is unpublished.
    Sharil · 5 years ago
    Oh its amazing, i didn't knew about to remove the space between words in a cell....its so easy...thanku so much.
  • To post as a guest, your comment is unpublished.
    Ashok Kumar R · 5 years ago
    :-) good.. any move issue please mail me
  • To post as a guest, your comment is unpublished.
    Dinesh · 5 years ago
    i am using MS 2010 ,how to remove all space in excel. exmple also give
  • To post as a guest, your comment is unpublished.
    JUNAID · 5 years ago
    Thanks a lot for helping us!!!!!!!!! :-)
  • To post as a guest, your comment is unpublished.
    JUNAID · 5 years ago
    THANKS A LOT FOR HELPING US!!!!!!!!!!!!!!
  • To post as a guest, your comment is unpublished.
    Suresh · 6 years ago
    Thanks a lot for sharing this Tool :-)
  • To post as a guest, your comment is unpublished.
    KD · 6 years ago
    Thank You. Your help has reduced my effort a lot.
  • To post as a guest, your comment is unpublished.
    Henman · 6 years ago
    Neither of these suggestions work... except, I'm sure, the Kutools solution so conveniently suggested. What an awesome blog. Thanks.
  • To post as a guest, your comment is unpublished.
    Mr.Niekoo · 6 years ago
    How i can remove dots from my columns of excel.I want to remove all dots from full list of columns.The data consist on Phone no's list

    email me
    mr.niekoo@hotmail.com
  • To post as a guest, your comment is unpublished.
    rajesh · 6 years ago
    Great command, It is very usefull.
  • To post as a guest, your comment is unpublished.
    Maria · 6 years ago
    I don't have kutools and I tired all formulas, eventually I had the trailing spaces removed from behind a date. However, excel still does not recognize the entry as a "date" and cannot sort by date, even though the format is date. So I have to enter each cell and F2. We are talking thousands of cells in a column... Can anyone give suggestions?
    • To post as a guest, your comment is unpublished.
      Imran · 5 years ago
      [quote name="Maria"]I don't have kutools and I tired all formulas, eventually I had the trailing spaces removed from behind a date. However, excel still does not recognize the entry as a "date" and cannot sort by date, even though the format is date. So I have to enter each cell and F2. We are talking thousands of cells in a column... Can anyone give suggestions?[/quote]
      Select the column--> go to Data> Text to column> select delimited> next> next> change the date format as MDY or DMY as the case in your sheet> press fininsh.
  • To post as a guest, your comment is unpublished.
    M&M · 6 years ago
    Replace option is the best and easiest! Thanks for sharing the tips!
  • To post as a guest, your comment is unpublished.
    Yasar Arafath · 6 years ago
    Remove space before and after the cell content (Eg:___26350__)
    We can use this formula =TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," ")))
    • To post as a guest, your comment is unpublished.
      Manish · 6 years ago
      Thanks a lot:):):).... It saved a lot of time....keep on updating such useful and very rate instances!!!!!!
  • To post as a guest, your comment is unpublished.
    Sushen · 6 years ago
    Very use full command
  • To post as a guest, your comment is unpublished.
    carla · 6 years ago
    Thank you! very helpful. saved the day.
  • To post as a guest, your comment is unpublished.
    kirtan · 6 years ago
    tried all the options above. not working as there are spaces before and after number. Any other alternative?
    • To post as a guest, your comment is unpublished.
      Yasar Arafath · 6 years ago
      Try this one

      =TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," ")))
  • To post as a guest, your comment is unpublished.
    karan · 6 years ago
    very helpful :) and easy to understand

    ;-)
  • To post as a guest, your comment is unpublished.
    Anna · 6 years ago
    Very helpful!
    Thank you
  • To post as a guest, your comment is unpublished.
    GaryMonday · 7 years ago
    Thank you very much for the trick, very very useful, it makes my spreadsheet a lot neater.

    Thanks again.

    Gary
  • To post as a guest, your comment is unpublished.
    Prabhakar · 7 years ago
    Wow its fantastic small commands work very well
  • To post as a guest, your comment is unpublished.
    ilham · 7 years ago
    wow thanks ..useful :-) :):):):):):)
  • To post as a guest, your comment is unpublished.
    BISHNU · 7 years ago
    I like this tools very much
  • To post as a guest, your comment is unpublished.
    KALPESH SUTHAR · 7 years ago
    Thanks a lot....its really helpful..save lot of time... :-)
  • To post as a guest, your comment is unpublished.
    corpsman0000 · 7 years ago
    how do you remove spaces without removing the zeros that in the begining of values middle and end? i just want to remove the spaces in between the values only. i tried the above and the zeros disappeared.
    • To post as a guest, your comment is unpublished.
      Avi · 6 years ago
      Convert the cell into text format then use replace function
  • To post as a guest, your comment is unpublished.
    Ranjith Kumar · 7 years ago
    Thanks Very very use full
  • To post as a guest, your comment is unpublished.
    Eva · 7 years ago
    Thanks for finally talking about >How to remove spaces between character and numbers within cells in Excel?
  • To post as a guest, your comment is unpublished.
    Rebeccah · 7 years ago
    Find/replace doesn't work for me, which is why I'm googling this topic in the first place. Is there a setting somewhere that disables this?

    I want to delete ":" form the cells in a column (and "/" from the cells in another column). I ought to be able to highlight the column, ^H, type ":" (or "/") in the find field, leave the replace field blank, and click Replace All, and it should do it. Or Find Next/Replace/Replace/Replace through the cells one at a time. But it advances therough the cells but doesn't do anything. If I put something in the replace field, it will do the replace, but it won't replace with an empty string.
  • To post as a guest, your comment is unpublished.
    Stefan · 7 years ago
    Thank you, easy to understand. best on the web
  • To post as a guest, your comment is unpublished.
    Janardhan · 7 years ago
    Very easy to understand. Good to follow.
    • To post as a guest, your comment is unpublished.
      ashutosh · 6 years ago
      bullshit....doesn't work
      • To post as a guest, your comment is unpublished.
        Joel · 5 years ago
        Didnt work for me too, however found the right way. Select and highlight the space you want to get rid of. Copy this space and select the find and replace. Paste this space in find and replace with nothing. I guess it wasnt a space in the first place. Whatever it was you now find it by copying whatever it was. Hope it works for you.
        • To post as a guest, your comment is unpublished.
          Ramesh Kumar P · 4 years ago
          It really worked....I tried may function like Clean, Trim, Substitute, etc
        • To post as a guest, your comment is unpublished.
          Jay · 4 years ago
          THAT WORKED! Thanks man
        • To post as a guest, your comment is unpublished.
          kati · 5 years ago
          thanks soo much. you are a genius!!!!
        • To post as a guest, your comment is unpublished.
          Imran · 5 years ago
          [quote name="Erika"]It worked for me! Thank you![/quote]
          Thank you so much for that. This saved a lot of time
        • To post as a guest, your comment is unpublished.
          Erika · 5 years ago
          It worked for me! Thank you!
  • To post as a guest, your comment is unpublished.
    Muhibullah Afzalzada · 7 years ago
    Thanks a lot, the Replace option worked as a charm... you really saved loads of my time.
  • To post as a guest, your comment is unpublished.
    Muhibullah Afzalzada · 7 years ago
    Thanks a lot, I really needed and the Replace option was a charm.
    • To post as a guest, your comment is unpublished.
      Stephen · 4 years ago
      Thanks a lot for the explanation - saved our dept a lot of work.