提示:其它语言是由 Google 机器翻译的。 你可以访问 English 版本。
登录
x
or
x
x
马上登记
x

or

如何删除单元格中的文本字符串的第一个/最后一个单词?

DOC  - 删除 - 第一字1

如果工作表中有一列文本字符串,则需要从单元格中的文本字符串中删除第一个或最后一个单词,如下面的屏幕截图所示。 Excel不提供直接的方式给你,但是,通过本教程中的以下公式,这个问题将尽快解决。

用公式从文本字符串中删除第一个或最后一个单词


按特定分隔符将单元格值拆分为多个列和行:

您订购的 Kutools for Excel拆分单元格 实用程序,您可以通过空格,逗号,换行符和您指定的其他分隔符将单元格中的文本字符串快速拆分为多个列或行。

doc-split-cells-1 1

Kutools for Excel:比200方便的Excel加载项,可以在60天免费试用。 下载并免费试用现在!


箭头蓝色右泡 用公式从文本字符串中删除第一个或最后一个单词


以下公式可能会帮助您从文本单元格中删除第一个或最后一个单词,请按照下列步骤操作:

从文本字符串中移除第一个单词:

1。 请将此公式输入到要放置结果的空白单元格中: =右(A2,LEN(A2)-FIND(“”,A2)) (A2 是具有要删除第一个字的文本字符串的单元格),请参阅截图:

DOC  - 删除 - 第一字2

2。 然后将填充手柄向下拖动到您要应用此公式的范围,并从单元格中删除了第一个单词,请参阅截图:

DOC  - 删除 - 第一字3

从文本字符串中删除最后一个单词:

要删除单元格中的最后一个单词,请复制并粘贴以下公式: =LEFT(TRIM(A2),FIND("~",SUBSTITUTE(A2," ","~",LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))))-1),然后将填充句柄拖到要包含此公式的范围,并将所有最后一个单词从单元格中删除。 看截图:

DOC  - 删除 - 第一字4


相关文章:

如何从Excel中的文本中删除第一个,最后一个或某些字符?


Kutools for Excel - 最佳办公生产力工具提高80%的生产力

  • 重用: 快速插入 复杂的公式,图表 以及你以前用过的任何东西; 加密单元格 密码; 创建邮件列表 并发送电子邮件...
  • 超级方程式酒吧 (轻松编辑多行文字和公式); 阅读布局 (轻松读取和编辑大量单元格); 粘贴到过滤范围...
  • 合并单元格/行/列 不丢失数据; 分裂细胞含量; 组合重复的行/列...防止重复的细胞; 比较范围...
  • 选择复制或唯一 行; 选择空行 (所有细胞都是空的); 超级查找和模糊查找 在许多工作簿中; 随机选择......
  • 精确复制 多个单元格而不更改公式参考; 自动创建参考 多张表; 插入项目符号,复选框等等......
  • 提取文本,添加文本,按位置删除, 删除空间; 创建和打印分页小计; 在单元格内容和注释之间转换...
  • 超级过滤器 (将过滤方案保存并应用到其他工作表); 高级排序 按月/周/日,频率等; 特殊过滤器 用粗体,斜体......
  • 结合工作簿和工作表; 根据键列合并表; 将数据拆分为多个表格; 批量转换xls,xlsx和PDF...
  • 超过300强大的功能。 支持Office / Excel 2007-2019和365。 支持所有语言。 在您的企业或组织中轻松部署。 全功能60天免费试用。
kte tab 201905

Office选项卡为Office提供选项卡式界面,使您的工作更轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,Publisher,Access,Visio和Project。
  • 在同一窗口的新选项卡中打开并创建多个文档,而不是在新窗口中。
  • 通过50%提高您的工作效率,每天为您减少数百次鼠标点击!
官方底部
Say something here...
symbols left.
You are guest ( Sign Up? )
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.
    Muhammad Talha · 1 months ago
    Retention Payable - Tooba Aluminium & Glass Works. L.L.C

    I want to remove (retention payable - ) from the above sentence

    what I will do I want to save my time
    • To post as a guest, your comment is unpublished.
      skyyang · 1 months ago
      Hello, Muhammad,
      To solve your problem, please apply the following formula:

      =MID(A1,1+FIND("~",SUBSTITUTE(A1," ","~",3)),255)

      Please try it, hope it can help you!
  • To post as a guest, your comment is unpublished.
    Mark K · 4 months ago
    I have two strings, one works fine removing the last word (the file size) but one does not (I think due to double space in the string.)

    =LEFT(TRIM(A2),FIND("~",SUBSTITUTE(A2," ","~",LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))))-1)

    Cell A2 contents: D:\Sowdeswari\BIO-LAB-DSM-03\03-Jan-2019\Config\SecEvent.Evt 01/01/2019 5963776
    Result: D:\Sowdeswari\BIO-LAB-DSM-03\03-Jan-2019\Config\SecEvent.Evt 01/01/2019 (This is correct)

    Cell A2 contents: D:\laptop_swap\win_account\Desktop\ !!!Docs\FORM-00538.pdf 18/01/2018 298032
    Result: D:\laptop_swap\win_account\Desktop\ !!!Docs\FORM-00538.pdf (this is not correct, but works when I remove one of the spaces before the "!!!" in the string)

    All help appreciated. It's a large file and there are a few errors but if I can fix this I can probably work out the other ones.
  • To post as a guest, your comment is unpublished.
    Mick H · 10 months ago
    Wow, thank you, worked perfectly and saved a lot of time.
  • To post as a guest, your comment is unpublished.
    Marlon Cruz · 11 months ago
    hello
    may i ask you how to separate using formula the 4-digit numbers?
    1234 · Bike:5678 · Tires Improvement; :6767
  • To post as a guest, your comment is unpublished.
    megz · 1 years ago
    AMINODDIN MAMONDIONG/HASSANA AMPUAN <<--- i want to separate this two names damn! let me try
  • To post as a guest, your comment is unpublished.
    Keiko Schrottke · 1 years ago
    Worked perfectly! Thank you very much.
  • To post as a guest, your comment is unpublished.
    JAMES · 3 years ago
    INSTEAD OF "," TRY USING ";"
  • To post as a guest, your comment is unpublished.
    vickyf. · 3 years ago
    hi!
    my problem is how to extract these names into first name , middle initial followed by last name.

    Roxas, Richard Jr. Alvarez
    Chavez, Ryan James Galareta
    Legaspi, Christian Arnaiz
    Jimenez, Johnray Del Castillo
    De Chavez, Rosalyn David
    Dela Rosa, Ana Joy Ramirez
    Manolo, Ma. Ana Santos
    Santillan, Jeneth Umali


    Result should be:

    Richard A. Roxas Jr.
    Ryan James G. Chavez
    Christian A. Legaspi
    Johnray D. Jimenez
    Rosalyn D. De Chavez
    Ana Joy R. Dela Rosa
    Ma. Ana S. Manolo
    Jeneth U. Santillan
  • To post as a guest, your comment is unpublished.
    Michael M. · 3 years ago
    Hi!
    I have issues applying the formula for removing the first word
    =RIGHT(A2,LEN(A2)-FIND(" ",A2))
    When I insert it into Excel it opens an error window, saying that the formula is incorrect and marking =RIGHT([b]A2,LEN[/b](A2)-FIND(" ",A2))
    Why???