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

or

如何从Excel中的单元格中删除特定字符之前或之后的文本?

本文介绍了从Excel中的单元格中删除特定字符或第n个出现字符之前或之后的文本的不同方法。


从Excel中的单元格中轻松删除所有数字,非数字或指定的字符

Kutools for Excel's 删除角色 实用程序可帮助您轻松删除Excel中所选单元格中的所有数字,非数字或指定字符。
立即下载Kutools for Excel的全功能60天免费试用版!


通过Excel中的“查找和替换”删除特定字符之前或之后的文本

要使用“查找和替换”功能删除特定字符之前或之后的所有文本,请执行以下操作。

1。 选择要在特定字符之前或之后删除文本的单元格,按 按Ctrl + H 键打开 查找和替换 对话。

在“查找和替换”对话框中:

1。 要删除逗号之类的特定字符之前的所有字符,请键入 *, 查找内容 文本框;

2。 要删除逗号等特定字符后的所有字符,请键入 ,*查找内容 文本框;

备注:

1。 您可以根据需要将逗号更改为任何字符。

2。 在这种情况下,将从所选单元格中删除最后一个逗号之前或第一个逗号之后的所有文本。

2。 保持 更换 文本框为空,然后单击 “全部替换” 按钮。 看截图:

在Office中进行选项卡式编辑和浏览
---让您的工作更轻松
Office Tab 在浏览和编辑多个文档时,将节省50%的工作时间。
难以置信的! 两个或多个文档的操作甚至比单个文档操作更令人愉快。
每天减少成千上万的键盘和鼠标操作,现在告别职业病。
Office Tab的界面比Internet浏览器更强大,更高效。

按公式删除第一个/最后一个特定字符之前或之后的文本

本节将向您显示从Excel中的单元格中删除第一个/最后一个特定字符之前或之后的所有内容的公式。

删除第一个逗号之前的所有内容, 请:

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

分子式: 删除第一个逗号之前的所有内容

= RIGHT(B5,LEN(B5)-找(”,",B5))

备注:

1。 在上面的公式中,B5是您将从中删除文本的单元格,“,”是您将基于文本删除文本的字符。

2。 要删除最后一个特定字符前的所有字符,请使用以下公式:

= RIGHT(B5,LEN(B5) - 查找( “@”,替代(B5,"字符”, “@”,(LEN(B5)-LEN(SUBSTITUTE(B5,"字符“ ”“)))/ LEN(”字符“))))

删除第一个逗号后的所有内容, 请:

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

分子式: 删除第一个逗号后的所有内容

= LEFT(B5,FIND( “”,B5)-1)

备注:

1。 在上面的公式中,B5是您将从中删除文本的单元格,“,”是您将基于文本删除文本的字符。

2。 要删除最后一个特定字符后的所有字符,请使用以下公式:

= LEFT(B5,FIND( “@”,替代(B5,"字符”, “@”,LEN(B5)-LEN(SUBSTITUTE(B5,"字符”, “”)))) - 1)


按公式删除第n个出现字符之前或之后的文本

以下公式可以帮助从Excel中的单元格中删除第n个出现字符之前或之后的所有字符。

从单元格中删除第n个出现字符之前的所有字符, 你需要:

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

分子式: 在第二次出现逗号之前删除所有内容

= RIGHT(SUBSTITUTE(B5,“,CHAR(9), 2),LEN(B5) - 查找(CHAR(9),SUBSTITUTE(B5,“,CHAR(9), 2),1)+ 1)

备注:

1。 在公式中, B5,“而 2 number表示第二次出现逗号后的所有内容都将从单元格B5中删除。

2。 你可以改变“,“而 2 根据需要编号到任何字符和出现位置编号。

从单元格中删除第n个出现后的所有字符, 你需要:

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


分子式: 在第二次出现逗号后删除所有内容

= LEFT(SUBSTITUTE(B5,",”,CHAR(9),2),FIND(CHAR(9),替代(B5,",”,CHAR(9),2),1)-1)

备注:

1。 在公式中, B5,“而 2 number表示第二次出现逗号后的所有内容都将从单元格A7中删除。

2。 你可以改变“,“而 2 根据需要编号到任何字符和出现位置编号。


使用Kutools for Excel轻松删除特定字符之前/之后的文本

如果某个范围内的每个单元格只有一个逗号分隔符,并且您想从单元格中删除此逗号之前或之后的所有内容,请尝试 拆分单元格 实用程序 Kutools for Excel。 此实用程序将帮助您只需点击几下即可解决问题:

1。 选择要在逗号之前或之后删除所有内容的单元格,然后单击 Kutools > 文本 > 拆分单元格。 看截图:

2。 在里面 拆分单元格 对话框中选择 拆分为列 在选项 类型 部分,并在 拆分 部分,选择 其他 选项并在空白框中键入逗号,然后单击 OK 按钮。 看截图:

3。 另一个 拆分单元格 弹出对话框,选择一个空白单元格来查找文本,然后单击 OK 按钮。

然后你可以看到选定的单元格被特定的字符 - 逗号分隔。 看截图:

提示。 如果您想获得此实用程序的免费试用(60日), 请点击下载,然后按照上述步骤去应用操作。


Kutools for Excel - 帮助您提前完成工作,有更多时间享受生活
您是否经常发现自己在追赶工作,没有时间为自己和家人度过难关? Kutools for Excel 可以帮助您处理80%Excel谜题并提高80%工作效率,让您有更多时间照顾家庭,享受生活。
适用于300工作场景的1500高级工具,使您的工作变得前所未有的轻松。
不再需要记忆公式和VBA代码,从现在起让你的大脑休息一下。
复杂和重复的操作可以在几秒钟内完成一次性处理。
每天减少成千上万的键盘和鼠标操作,现在告别职业病。
成为3分钟的Excel专家,帮助您快速获得认可和加薪促销。
110,000高效人才和300 +世界知名公司的选择。
让您的$ 39.0价值超过$ 4000.0其他人的培训。
全功能免费试用60天。 无理由的60日退款保证。

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.
    Kathleen · 13 days ago
    I have: E4U19-31C20010093021EI 3032AER LINGUS 190805000010. I need to remove everything before the first space and everything after the last space but I need to keep the spaces. I have this formula but it removes the spaces: =LEFT(RIGHT(A19,LEN(A19)-FIND(" ",A19)),FIND("^^",SUBSTITUTE(RIGHT(A19,LEN(A19)-FIND(" ",A19))," ","^^",LEN(RIGHT(A19,LEN(A19)-FIND(" ",A19)))-LEN(SUBSTITUTE(RIGHT(A19,LEN(A19)-FIND(" ",A19))," ",""))))-1)
  • To post as a guest, your comment is unpublished.
    Abdul Wahab · 16 days ago
    Great buddy. Too much helpful post.
  • To post as a guest, your comment is unpublished.
    Adrian · 4 months ago
    hello, I have the next string of numbers and characters in a cell.( 80E:1,85B:3,90B:3,90C:2,90D:2,95B:2,95C:2
    ) I want the cell to remain so ( 80E, 85B, 90B, 90C, 90D, 95B, 95C ) what formula I have to apply or how to proceed. Thanks a lot !
    • To post as a guest, your comment is unpublished.
      crystal · 3 months ago
      Hi Adrian,
      Sorry can't help you solving this problem yet. Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    Ivy · 6 months ago
    How do I create a multiple if search functtion where I place a left or righr function after a specific criteria. For example if the number starts in 501 then left 5, if the starts in 303 then left 6 and all other numbers are left 4. I have,
    • To post as a guest, your comment is unpublished.
      crystal · 6 months ago
      Hi Ivy,
      Would you please provide a screenshot of your spreadsheet showing what you are exactly trying to do? Thanks for commenting.
  • To post as a guest, your comment is unpublished.
    krish srinivasan · 10 months ago
    Hi, I need help.
    I have data extracted and posted in excel like this: (cell B2)
    NWS_141118_FTP_DMP_SAMY_MURUGAN_FISTUVEL_1

    I want to extract only the text (characters, may be 2, 3 , 4 in length), between the third _ and fourth _.
    Any help is appreciated.
    Thanks in anticipation
    • To post as a guest, your comment is unpublished.
      Jeyakumar Kannan · 7 months ago
      Given String: NWS_141118_FTP_DMP_SAMY_MURUGAN_FISTUVEL_1

      Desired to extract : DMP

      Position of 3rd _ : 16 : Try this formula =FIND("_",E6,FIND("_",E6,FIND("_",E6,1)+1)+1)+1

      Position of 4th _ : 19 : Try this formula =FIND("_",E6,FIND("_",E6,FIND("_",E6,FIND("_",E6,1)+1)+1)+1+1)
    • To post as a guest, your comment is unpublished.
      Jku · 7 months ago
      Given String: NWS_141118_FTP_DMP_SAMY_MURUGAN_FISTUVEL_1

      Desired to extract: DMP

      Position of 3rd _ : 16 : Please try this function =FIND("_",E6,FIND("_",E6,FIND("_",E6,1)+1)+1)+1

      Position of 4th _ : 19 : Please try this function =FIND("_",E6,FIND("_",E6,FIND("_",E6,FIND("_",E6,1)+1)+1)+1+1)
    • To post as a guest, your comment is unpublished.
      BOG · 7 months ago
      You can use excels inbuilt text to columns for this and just use, "_" as the separator
  • To post as a guest, your comment is unpublished.
    Neil · 11 months ago
    How do I only recall everything after the last underscore? So in this case I need just the 36D from the end. Example: 32533WHT_Caress36D_White_36D


    Thanks in advance!!
    • To post as a guest, your comment is unpublished.
      crystal · 8 months ago
      Hi Neil,
      This formula can help you: =RIGHT(A15,LEN(A3)-FIND("@",SUBSTITUTE(A15,"_","@",(LEN(A15)-LEN(SUBSTITUTE(A15,"_","")))/LEN("_"))))
  • To post as a guest, your comment is unpublished.
    Andes · 1 years ago
    I just wanna say thank you here, it's awesome using these formula with your help.. Thanks ^_^
  • To post as a guest, your comment is unpublished.
    Mina · 1 years ago
    Hey I got a text like this how do I remove everything after the hyphen?
    ABCD123456-ABC

    How do I delete or move everything after the 2 hyphen?
    ABCD12345-1234-ABCD
    ABCD12345-1234-X-123-AB-1

    Thank you in advance
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi Mina,
      To remove everything after the first hyphen, please apply this formula: =LEFT(A1,FIND("-",A1)-1)
      To remove everything after the second hyphen, this formula can help you: =LEFT(A1,SEARCH("-",A1,SEARCH("-",A1)+1)-1)
      • To post as a guest, your comment is unpublished.
        Peter · 7 months ago
        My excel is on PT-BR but this formula doesnt work, im using EXCEL 07, does i need a plugin or something to make that work?
      • To post as a guest, your comment is unpublished.
        Mina · 1 years ago
        Thank you life saver!
  • To post as a guest, your comment is unpublished.
    Ryan · 1 years ago
    Thank you :)
  • To post as a guest, your comment is unpublished.
    Marites · 1 years ago
    Hi. I have am trying to use formula =LEFT(B5,FIND(".",B5)-1 but instead of "." I would like to use "/" so it will take up only the required text & number. For example:
    ABCD-5008/2 and XYZ-5010/2. I need to capture only ABCD-5008 and XYZ-5010. But when I use =LEFT(B5,FIND("/",B5)-1) it gives me #VALUE!. Please advise me how to approach this concern. Thank you very much!
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi Marites,
      The formula works well in my case. Which Office version do you use?
  • To post as a guest, your comment is unpublished.
    Pjotr · 1 years ago
    Hello.
    I have text string in one cell similar to this: example1, example2, example3, example4, example5, example6, example7, example8, example9.
    I need to extract text before 5th comma for example. So as a result i would have something like this: example1, example2, example3, example4, example5.
    any help?
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Good Day,
      The below formula can help you extracting all texts before the 5th comma in a cell.
      =LEFT(SUBSTITUTE(A26," ","-",5),FIND("-",SUBSTITUTE(A26," ","-",5),1)-1)
  • To post as a guest, your comment is unpublished.
    Artik · 1 years ago
    Hello, I need help with formula.
    Two columns in first information about address, in second I need just country.
    For example:
    XXX34, AAABBBCCC VS, Šveice
    How I with formula can take just “Šveice”?
    Thanks!
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi Artik,
      This formula can help you: =TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),LEN(A1)))
  • To post as a guest, your comment is unpublished.
    Jonathan Piette · 1 years ago
    Haaaaaa I got it. I still don't know what I was doing wrong but I used different formula and it works. This is then a GOOD method to split cell in 3 or more categories using formula. Thanks to you crystal
    Here's my new revised formulas.
    B21: =TRIM(LEFT(SUBSTITUTE(A21,"_",REPT(" ",99)),99))
    C21: =RIGHT(A21,LEN(A21)-FIND("_",A21)) cell to be hide
    D21: =TRIM(LEFT(SUBSTITUTE(C21,"_",REPT(" ",99)),99))
    E21: =TRIM(RIGHT(SUBSTITUTE(A21,"_",REPT(" ",99)),99))
    Result:
    A21 (source): Bay12_PRL_Cb
    B21: Bay21
    D21: PRL
    E21: Cb

    I'm sure there is a way to simplify this, but it works for me.
  • To post as a guest, your comment is unpublished.
    Jonathan Piette · 1 years ago
    I post a snapshot, but for some reason, it doesn't show it!!!
    So here's the formula:
    B21: =LEFT(A21,LEN(A21)-FIND("_",A21))
    C21: =RIGHT(A21,LEN(A21)-FIND("_",A21))
    D21: =LEFT(C21,LEN(C21)-FIND("_",C21))
    E21: =TRIM(RIGHT(SUBSTITUTE(A21,"_",REPT(" ",99)),99))
  • To post as a guest, your comment is unpublished.
    Jonathan Piette · 1 years ago
    Hi Guys
    I try to split multiple element from a cell using forumas shown on this page, but I have problems, I have inconstancy and caractere missing! Check the snapshots to see the formula. Here's for example row 21 situation:
    A21: Bay12_PRL_Cb is my source cell. I want to split Bay21/PRL/Cb in 3 cells
    B21: Bay12_ (The "_" should not be there)
    C21: PRL_Cb (This one works fine, I will use and hide this cell to create D21)
    D21: PR (I should have PRL, the L is missing)
    E21: Cb (This one works fine)

    Can anyone point me what I'm doing wrong??
    This is excel mac 2008, version 12.3.2 Licensed
  • To post as a guest, your comment is unpublished.
    dharmendra · 1 years ago
    how to right STVP1-AMDC1-DELC2- value remove (-)
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Good Day,
      Do you mean remove all dash "-" from the string?
      Please try this formula =SUBSTITUTE(A1,"-","")
  • To post as a guest, your comment is unpublished.
    MakoSipper · 1 years ago
    Bear in mind that, with
    =RIGHT(A1,LEN(A1)-FIND(",",A1))
    you're keeping the space after the comma. An easy solution would be:
    =RIGHT(A1,LEN(A1)-FIND(",",A1)-1)
  • To post as a guest, your comment is unpublished.
    Atul · 1 years ago
    I have multiple / in my string and want to separate the text or string after the last / found in the string, please tell me how to do this
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Good Day,
      Supposing your cell is A4, you can apply this formula =LEFT(A4,FIND("@",SUBSTITUTE(A4,"/","@",LEN(A4)-LEN(SUBSTITUTE(A4,"/",""))))-1) to get all text before the last / symbol.

      and then apply formula =TRIM(RIGHT(SUBSTITUTE(A4,"/",REPT(" ",99)),99)) to get text after the last / symbol found in the string. See screenshot:
  • To post as a guest, your comment is unpublished.
    TarunKumar · 1 years ago
    Please share the formula for finding multiple spaces in a text string, to extract what we wish from that string, easily.
    Thankyou
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Dear TarunKumar,
      Sorry I am not sure I got your question. Would be nice if you could provide a screenshot of what you are trying to do.
  • To post as a guest, your comment is unpublished.
    Tom · 2 years ago
    This was very useful indeed to help me create / extract new logins from our email database - thank you! (and thanks, Excel)!
  • To post as a guest, your comment is unpublished.
    Barnett Frankel · 2 years ago
    I need to delete all text after the first word.
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Dear Barnett Frankel,

      If you assume that a space is the division between words, this formula =LEFT(A2,FIND(" ",A2&" ")-1) can help you. And if your words are separated by others such as comma, please replace the spaces in the formula with commas: =LEFT(A2,FIND(",",A2&",")-1)

      Best Regards, Crystal
  • To post as a guest, your comment is unpublished.
    Ken · 2 years ago
    The formula for deleting text after a character is exactly what I needed. Thank you!
  • To post as a guest, your comment is unpublished.
    Otep · 3 years ago
    Thank you for this! Got to save some precious time for a 600 line item.
  • To post as a guest, your comment is unpublished.
    Mariela · 3 years ago
    I like this way better than the mid formula!
  • To post as a guest, your comment is unpublished.
    Mohammed · 3 years ago
    So grateful to you, that was helpful
  • To post as a guest, your comment is unpublished.
    Vinoda · 4 years ago
    It is too helpfull .
  • To post as a guest, your comment is unpublished.
    willie gluck · 4 years ago
    Some great tips there! thanks.
  • To post as a guest, your comment is unpublished.
    Bill Liew · 4 years ago
    A big thank you. Perfect solution.
  • To post as a guest, your comment is unpublished.
    Sean · 4 years ago
    Fantastic, thanks for that! So easy