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) 进入它,然后按 输入 键。 然后拖动 填充手柄 将该公式应用于其他单元格。 看截图:
备注:B3是包含要删除空格的文本的单元格。

现在,所有多余的空格(包括前导,结尾和中间多余的空格)都将从指定的单元格中删除。

备注:此操作需要在新列中完成。 删除所有多余的空格后,您需要通过按 按Ctrl + C,选择原始数据范围,然后右键单击以选择 价值观 在下面 粘贴选项 部分。

单击几下以删除单元格中不需要的空间

随着 删除空间 实用程序,只需单击几下就可以帮助您不仅删除 前导空格,尾随空格,多余空格 而且还 所有空间 一个范围,多个范围甚至整个工作表,这将节省大量工作时间。
Kutools for Excel -收集了300多种强大的高级功能,专为1500多个工作方案而设计,可解决80%的Excel问题。

下载并尝试30天的全功能免费试用

 

仅删除单元格中的前导空格

在某些情况下,您可能只需要删除前导空格,并将所有中间空格保留在单元格中,如下面的屏幕截图所示。 本节介绍解决此问题的另一个公式和VBA代码。

使用MID功能仅删除前导空格

选择一个空白单元格(这里我选择单元格D3),将以下公式复制到其中,然后按 输入 键。 然后拖动 填充手柄 将该公式应用于其他单元格。

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

在这个公式中, B3 是包含要删除前导空格的文本的单元格。

备注:此操作需要在新列中完成。 删除所有前导空格后,您需要使用不带公式的修剪后的数据替换原始数据。

使用VBA代码仅删除前导空格

1.打开包含要删除前导空格的单元格的工作表,然后按 其他 + F11 键打开 Microsoft Visual Basic应用程序 窗口。

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应用程序 窗口。

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])

选择一个空白单元格,将以下公式复制到其中,然后按 输入 键。 然后拖动 填充手柄 将该公式应用于其他单元格。

=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 · 4 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 · 5 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 · 6 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 · 6 years ago
    Wow its fantastic small commands work very well
  • To post as a guest, your comment is unpublished.
    ilham · 6 years ago
    wow thanks ..useful :-) :):):):):):)
  • To post as a guest, your comment is unpublished.
    BISHNU · 6 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 · 4 years ago
          thanks soo much. you are a genius!!!!
        • To post as a guest, your comment is unpublished.
          Imran · 4 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.