如何在Excel中將文字和數(shù)字分開，只提取出數(shù)字部分

在日常的Excel工作中，很多時(shí)候我們需要處理包含數(shù)字和文字混合的數(shù)據(jù)。比如，某些數(shù)據(jù)列可能包含了既有數(shù)字又有字母或其他符號(hào)，如何將其中的數(shù)字提取出來(lái)，成為一個(gè)高效的工作需求。尤其是當(dāng)這些數(shù)據(jù)需要進(jìn)行進(jìn)一步的分析或匯總時(shí)，將文字與數(shù)字分開變得尤為重要。本文將詳細(xì)介紹幾種方法來(lái)提取Excel單元格中的數(shù)字部分，無(wú)論是利用內(nèi)置的函數(shù)，還是通過(guò)VBA腳本編程，都會(huì)一一為大家展示，并且確保這些方法既簡(jiǎn)單又實(shí)用。

1. 使用Excel內(nèi)置函數(shù)提取數(shù)字

Excel本身提供了一些非常實(shí)用的文本處理函數(shù)，借助這些函數(shù)，你可以很方便地將單元格中的數(shù)字部分提取出來(lái)。最常見的幾種方法包括使用`MID`、`TEXTJOIN`、`SUBSTITUTE`以及`VALUE`函數(shù)。

1.1 MID函數(shù)與LEN函數(shù)結(jié)合使用

`MID`函數(shù)可以從一個(gè)字符串中提取出指定位置的子字符串。通過(guò)結(jié)合`LEN`函數(shù)和`MID`函數(shù)，可以在一個(gè)字符串中提取出其中的數(shù)字部分。以下是常見的應(yīng)用步驟：

假設(shè)你需要從單元格A1中提取數(shù)字部分，可以使用如下公式：

“`

=MID(A1, FIND(“0”, A1), LEN(A1))

“`

此公式的工作原理是先通過(guò)`FIND`函數(shù)找到數(shù)字的起始位置，再通過(guò)`LEN`函數(shù)來(lái)提取后續(xù)的數(shù)字。

1.2 使用TEXTJOIN函數(shù)提取數(shù)字

如果單元格中存在混合字符，`TEXTJOIN`函數(shù)也是一個(gè)非常有用的工具。該函數(shù)能夠?qū)⒍鄠€(gè)字符連接起來(lái)，并且可以忽略掉非數(shù)字字符。你可以使用以下公式：

“`

=TEXTJOIN(“”, TRUE, IF(ISNUMBER(MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)1), MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1), “”))

“`

這是一個(gè)數(shù)組公式，需要按`Ctrl+Shift+Enter`來(lái)輸入。這個(gè)公式的作用是檢查單元格A1中的每一個(gè)字符，判斷其是否為數(shù)字，如果是，則返回?cái)?shù)字字符，否則忽略。

1.3 使用SUBSTITUTE和VALUE函數(shù)

`SUBSTITUTE`函數(shù)能夠替換字符串中的特定字符，而`VALUE`函數(shù)則可以將數(shù)字字符串轉(zhuǎn)換為實(shí)際的數(shù)值。通過(guò)這些函數(shù)的結(jié)合，能夠提取出其中的數(shù)字部分。例如：

“`

=VALUE(SUBSTITUTE(A1,”非數(shù)字字符”,””))

“`

如果你知道要?jiǎng)h除哪些非數(shù)字字符，可以通過(guò)`SUBSTITUTE`直接替換它們，再用`VALUE`將結(jié)果轉(zhuǎn)換為數(shù)值。

2. 使用正則表達(dá)式提取數(shù)字

Excel本身并不直接支持正則表達(dá)式，但你可以借助VBA（Visual Basic for Applications）來(lái)擴(kuò)展Excel的功能，使用正則表達(dá)式提取數(shù)字部分。下面是一個(gè)通過(guò)VBA來(lái)提取數(shù)字的示例。

2.1 編寫VBA宏提取數(shù)字

1. 首先，按下`Alt + F11`進(jìn)入VBA編輯器。

2. 在“插入”菜單中選擇“模塊”。

3. 在模塊窗口中粘貼以下VBA代碼：

“`vba

Function ExtractNumbers(cell As Range) As String

Dim regEx As Object

Dim matches As Object

Set regEx = CreateObject(“VBScript.RegExp”)

regEx.IgnoreCase = True

regEx.Global = True

regEx.Pattern = “\d+” ‘ 匹配數(shù)字

Set matches = regEx.Execute(cell.Value)

If matches.Count > 0 Then

ExtractNumbers = matches(0)

Else

ExtractNumbers = “”

End If

End Function

“`

4. 完成后，關(guān)閉VBA編輯器，回到Excel工作表。在你需要提取數(shù)字的單元格中，使用如下公式：

“`

=ExtractNumbers(A1)

“`

此時(shí)，VBA宏將會(huì)返回單元格A1中的數(shù)字部分。

3. 使用Power Query提取數(shù)字

對(duì)于復(fù)雜的數(shù)據(jù)清洗和轉(zhuǎn)換任務(wù)，Excel的Power Query工具可以提供極大的幫助。通過(guò)Power Query，你可以非常輕松地提取出混合文本中的數(shù)字部分。下面是使用Power Query提取數(shù)字的基本步驟：

3.1 導(dǎo)入數(shù)據(jù)到Power Query

首先，選擇包含數(shù)據(jù)的單元格區(qū)域，然后點(diǎn)擊“數(shù)據(jù)”選項(xiàng)卡中的“從表/范圍”按鈕，將數(shù)據(jù)導(dǎo)入Power Query編輯器。

3.2 使用文本函數(shù)提取數(shù)字

在Power Query編輯器中，選擇需要提取數(shù)字的列，然后使用以下步驟：

1. 在“添加列”選項(xiàng)卡中，選擇“自定義列”。

2. 在自定義列公式框中，輸入如下公式：

“`m

Text.Select([ColumnName], {“0”..”9″})

“`

該公式會(huì)從指定列中提取所有數(shù)字字符，忽略其他非數(shù)字字符。

3.3 加載數(shù)據(jù)

完成上述步驟后，點(diǎn)擊“關(guān)閉并加載”按鈕，Power Query將會(huì)將提取出來(lái)的數(shù)字加載回Excel工作表。

4. 利用文本到列功能分隔數(shù)字和文本

Excel還提供了一個(gè)非常簡(jiǎn)單的功能——“文本到列”。雖然它并不直接支持提取數(shù)字，但如果數(shù)據(jù)格式比較規(guī)律，可以利用這個(gè)功能將文字和數(shù)字分開。

4.1 使用文本到列拆分?jǐn)?shù)據(jù)

1. 選中包含混合數(shù)據(jù)的列。

2. 在“數(shù)據(jù)”選項(xiàng)卡中，點(diǎn)擊“文本到列”。

3. 選擇“分隔符號(hào)”選項(xiàng)，點(diǎn)擊“下一步”。

4. 根據(jù)數(shù)據(jù)格式選擇適當(dāng)?shù)姆指舴ɡ缈崭?、逗?hào)等），然后點(diǎn)擊“完成”。

如果數(shù)據(jù)中的數(shù)字和文字之間有明顯的分隔符，那么“文本到列”功能可以有效地將數(shù)字部分與文本分開。

5. 總結(jié)

在Excel中提取數(shù)字是一個(gè)非常常見的需求，而根據(jù)不同的情況，我們可以選擇不同的解決方案。對(duì)于簡(jiǎn)單的文本處理，內(nèi)置函數(shù)如`MID`、`TEXTJOIN`、`SUBSTITUTE`和`VALUE`可以快速解決問(wèn)題；如果需要處理更復(fù)雜的數(shù)據(jù)，VBA腳本和Power Query則提供了更強(qiáng)大的功能。最后，Excel的“文本到列”功能也能應(yīng)對(duì)一些簡(jiǎn)單的數(shù)據(jù)拆分需求。掌握了這些方法后，你可以更高效地清洗和處理Excel中的數(shù)據(jù)，提升工作效率。

無(wú)論你是數(shù)據(jù)分析師、財(cái)務(wù)人員，還是其他需要處理大量數(shù)據(jù)的用戶，學(xué)會(huì)如何提取Excel中的數(shù)字，將是你日常工作中不可或缺的技能。