Note: The other languages of the website are Google-translated. Back to English

從文本字符串中去除或去除非數字字符

有時,您可能需要從文本字符串中刪除所有非數字字符,並僅保留數字,如下圖所示。 本文將介紹一些用於解決Excel中此任務的公式。


使用公式從文本字符串中刪除或刪除所有非數字字符

在Excel 2019和Office 365中,新的TEXTJOIN函數與IFERROR,MID,ROW和INDIRECT函數結合使用可以幫助您僅從文本字符串中提取數字,通用語法為:

=TEXTJOIN("",TRUE,IFERROR(MID(text,ROW(INDIRECT("1:100")),1)+0,""))
  • text:您要從中刪除所有非數字字符的文本字符串或單元格值。

1。 請將以下公式複製或輸入到要輸出結果的空白單元格中:

=TEXTJOIN("",TRUE,IFERROR(MID(A2,ROW(INDIRECT("1:100")),1)+0,""))

2。 然後,按 Ctrl + Shift + Enter 鍵在一起以獲得第一個結果,請參見屏幕截圖:

3。 然後,選擇公式單元格,然後將填充手柄向下拖動到要應用此公式的單元格,僅提取數字,並去除所有其他非數字字符,請參見屏幕截圖:


公式說明:

ROW(INDIRECT(“ 1:100”):INDIRECT公式中的數字1:100表示​​MID函數計算文本字符串的100個字符。 該數組將包含100個數字,例如:{1; 2; 3; 4; 5; 6; 7; 8 .... 98; 99; 100}。
備註:如果您的文本字符串更長,則可以根據需要將數字100更改為更大的數字。

MID(A2,ROW(INDIRECT(“ 1:100”)),1:此MID函數用於提取單元格A2中的文本以得到一個字符,它將是這樣的數組:
{“ 5”;“ 0”;“ 0”;“”;“ K”;“ u”;“ t”;“ o”;“ o”;“ l”;“ s”;“”;“ f” ;“ o”;“ r”;“”;“ E”;“ x”;“ c”;“ e”;“ l”;“”;“”;“”;“”;“”;“”。 ..}

MID(A2,ROW(INDIRECT("1:100")),1)+0:在使用此數組將文本強制為數字後,添加0值,數字文本值將轉換為數字,並且非數字值將顯示為#VALUE錯誤值,如下所示:
{“ 5”;“ 0”;“ 0”;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE! !;#值! !;#值!...}

IFERROR(MID(A2,ROW(INDIRECT("1:100")),1)+0:此IFERROR函數用於將所有錯誤值替換為一個空字符串,如下所示:
{“ 5”; “ 0”;“ 0”;“”;“”; “”;“”;“”;“”;“”;“”; …}

TEXTJOIN("",TRUE,IFERROR(MID(A2,ROW(INDIRECT("1:100")),1)+0,"")):最後,此TEXTJION函數將合併由IFFERROR函數返回的數組中的所有非空值,並返回結果。


筆記:

1.使用以上公式,數字將以文本格式返回,如果您需要一個實數值,請應用此公式,切記按 Ctrl + Shift + Enter 鍵在一起以獲得正確的結果。

=TEXTJOIN("",TRUE,IFERROR(MID(A2,ROW(INDIRECT("1:100")),1)+0,""))+0

2.在早期版本的Excel中,此公式將不起作用,在這種情況下,以下公式可能會對您有所幫助,請將該公式複製或輸入到空白單元格中:

=SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1)) * ROW(INDIRECT("1:"&LEN(A2))), 0), ROW(INDIRECT("1:"&LEN(A2))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(A2)))/10)


使用簡單的功能從文本字符串中剝離或刪除所有非數字字符

也許記住上面的公式太久了,在這裡,我將介紹 Excel的Kutools 對你來說 刪除字符 功能,只需單擊幾下即可從文本字符串中刪除數字,字母,不可打印或字母數字字符。 點擊下載Kutools for Excel免費試用!


使用的相對功能:

  • TEXTJOIN:
  • TEXTJOIN函數使用特定的分隔符將行,列或單元格區域中的多個值連接在一起。
  • MID:
  • MID函數用於從給定文本字符串的中間查找並返回特定數量的字符。
  • ROW:
  • Excel ROW函數返回引用的行號。
  • INDIRECT:
  • Excel INDIRECT函數將文本字符串轉換為有效的引用。
  • IFERROR:
  • IFERROR函數用於在公式計算錯誤時返回自定義結果,而在沒有錯誤發生時返回正常結果。

更多文章:


最佳辦公效率工具

Kutools for Excel-幫助您從人群中脫穎而出

您想快速,完美地完成日常工作嗎? Kutools for Excel具有300個強大的高級功能(合併工作簿,按顏色求和,拆分單元格內容,轉換日期等),並為您節省80%的時間。

  • 專為1500個工作方案而設計,可幫助您解決80%的Excel問題。
  • 每天減少數千次鍵盤和鼠標的點擊,減輕您疲倦的眼睛和手的疲勞。
  • 在3分鐘內成為Excel專家。 不再需要記住任何痛苦的公式和VBA代碼。
  • 30天無限制免費試用。 60天退款保證。 免費升級和支持2年。
Excel功能區(已安裝Kutools for Excel)

Office選項卡-在Microsoft Office(包括Excel)中啟用選項卡式閱讀和編輯

  • 一秒鐘即可在數十個打開的文檔之間切換!
  • 每天為您減少數百次鼠標單擊,告別鼠標手。
  • 查看和編輯多個文檔時,可將您的工作效率提高50%。
  • 像Chrome,Firefox和新的Internet Explorer一樣,為Office(包括Excel)帶來高效選項卡。
Excel的屏幕截圖(已安裝Office選項卡)
按評論排序
留言 (4)
還沒有評分。 成為第一位評論!
網站主持人對此評論進行了最小化
謝謝你。 不錯的公式。我將如何更改它,以便如果單元格僅包含字母,則公式輸入 0 結果單元格(而不是目前的空白)?我想我可以通過將公式包裝在另一個 IF 聲明,但我並沒有走得太遠。
網站主持人對此評論進行了最小化
你好,格倫,要將結果顯示為空白而不是零,請應用以下公式:=IF(SUM(LEN(A2)-LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"}, "")))>0, SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("$1:$"&LEN(A2))),1))* ROW(INDIRECT("$1:$"&LEN(A2))),0), ROW(INDIRECT("$1:$"&LEN(A2))))+1,1) * 10^ROW(INDIRECT("$1:$"&LEN(A2)))/10),"")

請嘗試,希望對您有所幫助!
網站主持人對此評論進行了最小化
哇,這需要一些消化😋感謝您抽出寶貴時間回复👍
網站主持人對此評論進行了最小化
這個法國設置的公式是什麼?
這裡還沒有評論
留下你的意見
以訪客身份發帖
×
評價此帖子:
0   字符
推薦地點