Excel如何提取產品編碼中的數字部分用于排序？

來源：通商軟件2025-07-222025-07-22

ERP系統 & MES 生產管理系統

10萬用戶實施案例,ERP 系統實現微信、銷售、庫存、生產、財務、人資、辦公等一體化管理

在進行產品管理和數據分析時，我們經常需要對產品編碼進行排序。特別是在一些包含字母和數字的產品編碼中，提取數字部分進行排序顯得尤為重要。Excel作為一款強大的數據處理工具，提供了許多便捷的函數和方法，幫助我們輕松提取產品編碼中的數字部分。本文將詳細介紹如何在Excel中提取產品編碼中的數字部分，并根據這些數字進行排序。

產品編碼中的數字提取問題

許多產品的編碼由字母和數字組成，像“ABC123”或“PROD2023”這樣的格式。直接對整個編碼進行排序時，Excel將字母部分視為文本，數字部分視為數值，導致排序結果并不符合我們的預期。例如，”PROD2″ 和 “PROD10” 被排序時會先出現“PROD10”，因為Excel將數字部分作為文本進行處理。這就要求我們必須從中提取出數字部分，作為排序依據。

步驟一：使用Excel函數提取數字部分

在Excel中提取編碼中的數字部分，我們可以借助Excel的文本函數來完成。最常見的兩種方法是使用`MID`函數和`TEXTJOIN`函數。

1. 使用MID函數提取數字

`MID`函數能夠從指定位置提取字符串中的一部分。假設產品編碼的數字部分始終在最后，且總是數字，我們可以使用類似下面的公式來提取數字部分：

“`

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

“`

這段公式使用了`MID`函數和一些數組公式，來提取文本中的數字部分。公式的重點是找到數字字符的位置，并從該位置開始提取后續的數字。

2. 使用TEXTJOIN函數（適用于Office 365和Excel 2021及更高版本）

對于包含多個數字且混雜在字母中的編碼，`TEXTJOIN`函數結合`IF`和`ISNUMBER`函數能夠提取所有的數字部分。假設單元格A1中的產品編碼為“ABC123XYZ”，可以使用如下公式：

“`

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

“`

這個公式通過逐字符掃描產品編碼，提取出所有數字并合并成一個字符串。

步驟二：將提取出的數字轉化為數值

在提取數字后，得到的仍然是文本格式，若要進行排序，需要將其轉化為數值。為此，可以使用`VALUE`函數將文本格式的數字轉化為數值格式。假設提取出的數字在B1單元格，我們可以使用如下公式將其轉化為數值：

“`

=VALUE(B1)

“`

這樣，Excel就會將提取到的數字部分轉換為數值格式，準備好用于排序。

步驟三：按提取出的數字進行排序

完成數字提取和轉換后，您就可以使用Excel的排序功能進行排序了。首先，選擇包含提取數字的列，然后點擊“數據”選項卡中的“排序”按鈕。確保選擇“按數值排序”選項，并選擇升序或降序排列。這時，Excel會根據提取出的數字部分進行排序，而忽略原本產品編碼中的字母部分。

進階技巧：動態提取數字部分

有時，產品編碼中的數字部分可能并不總是在編碼的最后，或者數字部分的長度不固定。為了應對這種情況，我們可以通過結合使用`ARRAYFORMULA`（或數組公式）和`MID`函數，進行更復雜的數字提取。

例如，在編碼“AB123CD456”中，數字部分分布在字母之間，我們可以使用以下數組公式進行提取：

“`

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

“`

這個公式會從中提取出所有數字，幫助我們動態地提取不同位置上的數字部分。此方法能夠靈活應對不同格式的產品編碼。

如何處理不同格式的編碼

產品編碼格式千變萬化，有些編碼可能包含特殊字符或前后空格，給提取數字帶來額外的困難。為了確保能夠順利提取數字，可以先使用`TRIM`函數去除空格，或使用`SUBSTITUTE`函數替換掉不需要的符號。

例如，如果編碼中包含了符號“-”，可以使用以下公式去除符號：

“`

=SUBSTITUTE(A1, “-“, “”)

“`

清除無關字符后，再進行數字提取，能夠避免錯誤的提取結果。

排序中的常見問題及解決方法

在按照數字部分排序時，可能會遇到一些問題，特別是在產品編碼的格式不一致時。常見的問題包括：

1. 提取結果包含非數字字符

有時，提取出來的數字部分可能仍然包含一些非數字字符，這會影響排序。為了解決這一問題，確保使用`VALUE`函數將數字部分轉化為數值，避免誤差。

2. 提取的數字不完整

如果產品編碼中的數字部分長度不一致，或者部分數字被誤提取為文本，可能導致排序錯誤。此時，可以調整公式，確保它能夠準確地提取所有需要的數字。

總結歸納

在Excel中提取產品編碼中的數字部分并進行排序，是數據分析和管理中的常見需求。通過使用Excel的文本函數，如`MID`、`TEXTJOIN`以及`VALUE`函數，我們可以靈活地提取并處理編碼中的數字部分，從而實現正確的排序。無論是簡單的數字提取，還是面對復雜格式的編碼，我們都可以通過不同的技巧和公式進行有效處理。掌握這些方法，將大大提高我們在數據分析和產品管理中的工作效率。