一文說盡Excel中文字格式的數字轉換技巧，建議收藏

在Excel中，我們經常遇到儲存為文字格式的數字。有的是人為輸入，有的是從某些系統中匯出時數字是文字格式。儲存為文字的數字可能導致意外的結果。

有多種辦法把這些文字轉換成數字格式，我根據不同使用場景，從易到難，一一整理如下。

01

最簡單方法是用滑鼠直接轉換

如上圖所示，選擇文字型數字所在的單元格，然後單擊左上角的黃色感嘆號，會彈出轉換為數字格式的選項，直接選擇該轉換選項，就可以把某個單元格或某一列的文字轉換為數字。

但有的時候該按鈕不可用，這時我們要尋找第二種辦法。

02

利用分列來轉換文字格式的數字

這種方法要注意，每次只能轉換一列。如果有多列存在文字數字，要進行多次操作，這時並不推薦這種辦法。

1。 選定列

選擇具有此問題的列。 如果不想轉換整列，可改為選擇一個或多個單元格。 只需確保所選單元格位於同一列中，否則此步驟不起作用。 （如果多個列中出現此問題，請參閱下面的其他轉化方式。）

2。單擊【分列】按鈕

“分列”按鈕通常用於拆分列，但也可用於將單列文字轉換為數字。 在“”選項卡上單擊“”。

3。單擊“完成”

“分列”嚮導步驟的其餘部分最適用於拆分列。 由於只是轉換列中的文字，隨即可單擊“

完成

”，Excel 將轉換單元格。

4。 設定格式

按 CTRL+1（或 Mac 上的 +1）。 然後選擇任意格式。

注意

:

如果仍看到未顯示為數字結果的公式，則可能已啟用“

顯示公式

”。 轉到“

公式

選項卡”，確保“

顯示公式

”已關閉。

03

利用選擇性貼上來轉換文字格式的數字

對於多列文字數字需要轉換，可以用選擇性貼上的辦法來一次性轉換。

方法是在任意一個空白單元格中輸入0，然後複製，選中要轉換成數字的區域，選擇性貼上，在【運算】區域選擇【加】或【減】，即可。

也可以在任意單元格輸入1，複製，然後選中要轉換的區域，選擇性貼上為【乘】或【除】。

原理也就是小學學過的四則運算規律——一個數加或減0，值不變，一個數乘以或除以1，值不變。

04

利用Value函式來轉換文字格式的數字

可使用 VALUE 函式僅返回文字的數值。

1。插入新列

在帶文字的單元格旁邊插入新列。 在本例中，列 E 包含儲存為數字的文字。 F 列是新列。

2。使用 VALUE 函式

在新列的某個單元格中鍵入=VALUE（），然後在括號中鍵入包含作為數字儲存的文字的單元格引用。 在本示例中，它是單元格 E23。

3。將游標停放在此處

現在將單元格的公式向下填充到其他單元格。 如果以前從未執行過此操作，操作方式如下：將游標停放在單元格的右下角，直到它變為加號。

4。單擊並向下拖動

單擊並向下拖動，將公式填充到其他單元格。 完成後，可使用此新列，也可將這些新值複製貼上到原始列。 操作方式如下：選擇包含新公式的單元格。 按 Ctrl + C。單擊原始列的第一個單元格。 在“開始”選項卡上，單擊“貼上”下的箭頭，然後依次單擊“選擇性貼上”和“值”。

05

用Excel的PowerQuery實現大批次、經常性的文字格式數字轉換工作

前面介紹的四種方法適用於單次或少量文字格式的數字轉換操作。

如果您經常要轉換固定格式的表格裡的文字數字，或者您有大量相同格式的表格要進行文字格式的數字轉換，那麼，Excel的PowerQuery現然更適合做這項工作。

Excel的PowerQuery能做的並不僅僅是文字格式轉換工作，它還能做很多其他涉及資料的收集、清洗和規範化，最後和PowerPivot聯動，將資料轉化成專業的圖表。

因此，它能夠節省大量的工作時間——只要第一次建立好資料模板，今後要做的就是原始資料更新之後重新整理一下即可，這樣從資料收集到清洗到建模到最後呈現，都透過一個簡單的重新整理動作中就完成了。

下面詳述步驟。

1.引入來自資料夾的資料來源

由於已經假設需要經常性地對大量表格進行文字數字格式的轉化工作，因此，不再能夠直接在原始資料上操作。

我們需要引入一個來自資料夾的源。

新建一個Excel檔案，然後在【資料】選項卡上選擇【獲取資料】，單擊【從資料夾】。

找到需要轉換的表格所在的資料夾。

點選確定後，就將原始資料引入PowerQuery了。

注意，引入來自資料夾的源時：

無論要處理的表格是分散在單個Excel的單個WorkSheets的不同表（table）中，還是分散在單個Excel檔案的不同WorkSheets中，乃至分散在不同的Excel檔案中；

無論這些Excel檔案是在要處理的資料夾的根目錄下，還是在任意層級的子資料夾中，都不影響我們後面的操作。

這就是PowerQuery的魅力所在。

2.在查詢預覽視窗直接點選【轉換資料】

，我們將提取資料的具體操作放到下一步。

3.在查詢編輯視窗，選中【Content】列

，因為我們需要的資料都在這一列裡，刪除其他列。

從上圖可以看到有兩個要轉換的表格。其實在那個副本里邊，還有多個WorkSheets和多個表（table）包含需要轉換文字數字的資料，待會兒就可以看到了。

4.在公式欄輸入【Excel.Workbook([Content])】提取資料

，【［Content］】可以直接從右邊的【可用列】窗口裡單擊它，它就自動新增到左邊公式輸入框對應的位置裡了。

5.展開自定義列，就可以看到所有要轉換數字格式的表格了。

展開後的結果如下：

可以看到有需要處理的Sheet，也有需要處理的Table。這些Tables在原始資料中是這樣的：

另外一個Sheet中還有兩個類似的表，所以我的示例資料中總共4個表。

注意，上面截圖中左邊還有個不是表的【區域】（region）。

並不建議在一個Sheet中區域（region）和表（table）同時出現。

因為同時出現的後果是，PowerQuery會把表（table）統計兩次，一次是在sheet中，一次是在table中。從而導致資料重複，或操作困難。

此處不展開。

有興趣的可以自己體驗下區域（region）和表（table）同時出現在一個Sheet上對PowerQuery操作的影響。

6.資料來源引入完畢。

最後我們得到所有Excel檔案中所有Sheets中需要處理的表格（無論來自於區域（region）還是表（table））資料。

可以看到PowerQuery已經提示這都是【文字數字】。

7.轉換格式的操作很簡單，只要一步：將其設定為所需的數字格式即可

。我給的例子中都是文字格式的日期，所以我將它們設定為日期格式：

得到的結果如下：

8。進行其他操作並載入。對於日常資料處理來說，不會單單隻轉換文字數字格式就結束了，還要進行其他資料清洗操作，或者資料建模工作。全部完成後就載入到Excel表或資料模型，進行資料的分析和視覺化展現。

9。重新整理。所有操作完成後，當原始資料所在的資料夾裡的檔案更新後，只要開啟這個資料模型檔案，點選【資料】選項卡上的【全部重新整理】，那麼我們前面做的所有工作以及做的資料分析和視覺化結果，都在幾十秒或幾分鐘內獲取到最新結果（取決於資料量和電腦配置，具體時間會有變化）。

06

總結

看起來用PowerQuery做文字格式轉換的操作是最多的，但其實這是因為我要演示給各位看，所以把步驟寫得非常詳細。當你熟悉之後，從引入資料來源到文字格式的數字轉換完畢，不要一分鐘就能完成，並不比前面四種方法耗時更多，而它所節省的時間，你是無法計量的。

想象一下你有一萬個Excel表格分散在數千個資料夾下面，你要對他們進行的操作不僅僅是轉換文字格式的數字，還有很多很多其他操作，你能想象這個工作量有多大嗎？

然而，藉助Excel的PowerQuery，無論檔案有多少個，操作有多複雜，你所要做的就是第一次建模時稍微多花一點時間，以後就只需要重新整理一下，僅僅這麼一個動作，就將這一萬個Excel中的資料處理成了你想要的圖表。這該是多麼幸福的一件事情。

所以，我再次為Excel的PowerQuery打call，經常被處理和分析資料搞得焦頭爛額、加班不止、頭髮掉了又掉的你，如果還沒用PowerQuery，就趕緊用起來吧。

如果你覺得我的帖子對你有幫助，請評論、轉發、收藏，如果能關注我的百家號，則感激不盡，謝謝

想了解更多精彩內容，快來關注高效office辦公