提示:其他語言是Google翻譯的。 你可以訪問 English 版本。
登入
x
or
x
x
註冊
x

or

如何在Excel中的第二個空格或逗號之前/之後提取文本?

在Excel中,“文本到列”功能可以幫助您通過空格,逗號或其他分隔符將每個文本從一個單元格提取到單獨的單元格中,但是,您是否嘗試從單元格中的第二個空格或逗號之前或之後提取文本在Excel中如下截圖所示? 這篇文章,我會談談一些處理這個任務的方法。

doc在第二個空格1之前提取

在公式第二個空格或逗號前面提取文本

在公式第二個空格或逗號後面提取文本


按特定分隔符將單元格值拆分為多個列和行:
Kutools for Excel拆分單元格 實用程序,您可以通過空格,逗號,換行符和您指定的其他分隔符將單元格中的文本字符串快速拆分為多個列或行。
doc分割單元格-1-1

箭頭藍色右泡 在公式第二個空格或逗號前面提取文本


要獲得第二個空格之前的文本,請使用以下公式:

輸入這個公式: = IF(ISERROR(FIND(“”,A2,FIND(“”,A2,1)+ 1)),A2,LEFT(A2,FIND(“”,A2,FIND(“”,A2,1)+ 1))) 到您想要查找結果的空白單元格中, C2,例如,然後將填充手柄向下拖動到要包含此公式的單元格,並從每個單元格提取第二個空格之前的所有文本,請參閱截圖:

doc在第二個空格2之前提取

備註:如果要在第二個逗號或其他分隔符之前提取文本,請根據需要使用逗號或其他分隔符替換公式中的空格。 如: =IF(ISERROR(FIND(",",A2,FIND(",",A2,1)+1)),A2,LEFT(A2,FIND(",",A2,FIND(",",A2,1)+1))).


箭頭藍色右泡 在公式第二個空格或逗號後面提取文本

要在第二個空格後返回文本,以下公式可以幫助您。

請輸入這個公式: = MID(A2,FIND(“”,A2,FIND(“”,A2)+ 1)+ 1,256) 放入一個空白單元格中以定位結果,然後將填充手柄向下拖動到單元格以填充此公式,並立即提取第二個空格後的所有文本,請參閱截圖:

doc在第二個空格3之前提取

請注意: 如果要在第二個逗號或其他分隔符之後提取文本,則只需在公式中根據需要用逗號或其他分隔符替換空格。 如: = MID(A2,FIND(“,”,A2,FIND(“,”,A2)+ 1)+ 1,256).


Kutools for Excel解決了您的大多數問題,並使您的生產率提高了80%

  • 重用: 快速插入 複雜的公式,圖表 以及你以前用過的任何東西; 加密單元格 密碼; 創建郵件列表 並發送電子郵件...
  • 超級方程式酒吧 (輕鬆編輯多行文字和公式); 閱讀佈局 (輕鬆讀取和編輯大量單元格); 粘貼到過濾範圍...
  • 合併單元格/行/列 不丟失數據; 分裂細胞含量; 組合重複的行/列...防止重複的細胞; 比較範圍...
  • 選擇複製或唯一 行; 選擇空行 (所有細胞都是空的); 超級查找和模糊查找 在許多工作簿中; 隨機選擇......
  • 精確複製 多個單元格而不更改公式參考; 自動創建參考 多張表; 插入項目符號,複選框等等......
  • 提取文本,添加文本,按位置刪除, 刪除空間; 創建和打印分頁小計; 在單元格內容和註釋之間轉換...
  • 超級過濾器 (將過濾方案保存並應用到其他工作表); 高級排序 按月/週/日,頻率等; 特殊過濾器 用粗體,斜體......
  • 結合工作簿和工作表; 根據鍵列合併表; 將數據拆分為多個表格; 批量轉換xls,xlsx和PDF...
  • 超過300強大的功能。 支持Office / Excel 2007-2019和365。 支持所有語言。 在您的企業或組織中輕鬆部署。 全功能30天免費試用。
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.
    demo · 4 months ago
    how do I do this from right to left. Basically, want to extract last two words.
    • To post as a guest, your comment is unpublished.
      skyyang · 1 months ago
      Hi, demo,
      To extract and return the last two words from text strings, please apply the below formula:
      =IF((LEN(A1)-LEN(SUBSTITUTE(A1," ","")))<2,A1,RIGHT(A1,LEN(A1)-FIND("/",SUBSTITUTE(A1," ","/",(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1)))))

      Please try, hope it can help you!
  • To post as a guest, your comment is unpublished.
    Alessandro · 10 months ago
    THANK YOU!!!
  • To post as a guest, your comment is unpublished.
    Tiki · 1 years ago
    Is there a way to extract various pieces of this string? 123ABC.01.02.03.04 ---- for example, to pull the 123ABC, and then in the next column pull 123ABC.01, and then 123ABC.01.02, then 123ABC.01.02.03, and so on.
  • To post as a guest, your comment is unpublished.
    Archi · 1 years ago
    Hi, is there a way, if I want to select text after 3rd comma from the end?
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hi, Archi,
      To extract all text after the 3rd comma, please apply this formula:
      =RIGHT(A1,LEN(A1)-FIND("@@@",SUBSTITUTE(A1,",","@@@",3)))

      Please try it.
  • To post as a guest, your comment is unpublished.
    Dave · 1 years ago
    =IF(ISERROR(FIND(",",A2,FIND(",",A2,1)+1)),A2,LEFT(A2,FIND(",",A2,FIND(",",A2,1)+1)))
    That will return all text left of the second comma plus the second comma. This should be

    =IF(ISERROR(FIND(",",A2,FIND(",",A2,1)+1)),A2,LEFT(A2,FIND(",",A2,FIND(",",A2,1)+1)-1))
    to omit the second comma

    1. Saquon Barkley, RB, Penn State
    2. Derrius Guice, RB, LSU
    3. Sony Michel, RB, Georgia
    4. Ronald Jones II, RB, USC
    5. Nick Chubb, RB, Georgia

    Bad:
    1. Saquon Barkley, RB,
    2. Derrius Guice, RB,
    3. Sony Michel, RB,
    4. Ronald Jones II, RB,

    Better:
    1. Saquon Barkley, RB
    2. Derrius Guice, RB
    3. Sony Michel, RB
    4. Ronald Jones II, RB
    • To post as a guest, your comment is unpublished.
      Ron · 7 months ago
      But this won't work if the text string does NOT always have a second comma...
  • To post as a guest, your comment is unpublished.
    Rodney · 2 years ago
    How would you change this to the 3rd comma? Instead of the 2nd?
    • To post as a guest, your comment is unpublished.
      skyyang · 2 years ago
      Hello,Rodney,
      To extract the text before the 3rd space, please apply this formula:
      =IF(ISERROR(FIND(" ",A2,FIND(" ",A2,FIND(" ",A2,1)+1) +1)),A2,LEFT(A2,FIND(" ",A2,FIND(" ",A2,FIND(" ",A2,1)+1) + 1)));
      To extract the text after the 3rd space, please use this formula:
      =MID(A2, FIND(" ", A2,FIND(" ", A2, FIND(" ", A2)+1) +1)+1,30000)
      Please try it, hope it can help you!
      Thanks!
  • To post as a guest, your comment is unpublished.
    Guni · 2 years ago
    Hi, this formula will be ideal for but instead of removing text after second space i want remove everything after 3rd i have been trying insert 3rd FIND(" ",A2 i understand that the formula itself is =FIND(" ",X13,1). can you please help me out. I am not good with nesting the formulas.

    Thank you very much.