如何在 Excel 中 vlookup 以文字形式存儲的數字？

在 Excel 中使用 VLOOKUP 時，遇到格式不匹配的情況——特別是當查找值以文字形式存儲而查找列為數字時，或者反之——會導致查找失敗或錯誤。這種格式不匹配是一個常見問題，特別是在數據來自外部來源、被導入或處理大型和協作數據集時。解決這些不匹配對於確保 VLOOKUP 按預期運行並幫助您檢索正確的信息至關重要。本逐步指南介紹了幾種實用的解決方案來應對這些格式不一致，無論您的工作簿中數字是如何存儲的，都能確保可靠且準確的查找。

本文將展示處理此類錯誤的有效方法，包括公式調整、內置的 Excel 工具以及用於批量或自動化處理的 VBA 自動化。我們還討論了每種方法的優點和考慮因素，幫助您選擇最適合您場景的方法。

• 使用公式 vlookup 以文字形式存儲的數字
• 使用 Kutools for Excel 快速修復格式不匹配問題
• VBA 宏：在 VLOOKUP 前標準化格式
• 其他內置的 Excel 方法：使用「文本轉欄」修復數據格式

使用公式 vlookup 以文字形式存儲的數字

如果您的查找數據包含一處以文字形式存儲的數字，另一處則是實際數字，由於這種格式不一致，VLOOKUP 可能無法找到匹配項。其中一個最直接的解決方案是使用 Excel 公式，該公式可以將查找值或查找列轉換為一致的格式。這種方法在大多數工作表操作中效果良好，應用方便，並且不會更改原始數據。

例如，如果您的查找值以文字形式存儲，而表格中的相應字段則格式化為數字，您可以使用 VALUE 函數將文字轉換為數字，並將其嵌套在 VLOOKUP 公式中。

在想要顯示結果的空白單元格中輸入以下公式：

=VLOOKUP(VALUE(G1),A2:D15,2,FALSE)

輸入公式後，按 Enter 鍵以檢索符合條件的值，如下方截圖所示：

參數解釋與提示：

• G1：包含您要查找的值的單元格（可以是文字或數字）。
• A2:D15：包含查找列和您希望返回信息列的數據表範圍。
• 2：從表格範圍最左列開始計算，您希望返回結果的列號。

請注意檢查查找值中的前後空格，因為這些也可能導致查找失敗。如果您的數據可能包含額外空格，請考慮結合使用 TRIM 函數。

如果查找值是真實的數字（數字格式），但表格中的相應字段則存儲為文字，則需要在執行查找之前將數字轉換為文字。TEXT 函數適用於此場景：

=VLOOKUP(TEXT(G1,0),A2:D15,2,FALSE)

在目標單元格中輸入此公式，按 Enter，然後如下面所示返回正確結果：

這裡，TEXT 函數內部的數字格式代碼“0”確保您的數字在匹配之前轉換為純文本值。

如果您不確定查找值的可能格式，或者您期望查找列中同時出現文字和數字，可以使用 IFERROR 函數嵌套兩種方法，無縫處理所有可能性：

=IFERROR(VLOOKUP(VALUE(G1),A2:D15,2,0),VLOOKUP(TEXT(G1,0),A2:D15,2,0))

在結果單元格中輸入此公式。它將首先嘗試通過將您的值轉換為數字進行查找；如果失敗（例如，如果該值不能強制轉換為數字），它將嘗試將您的值轉換為文字並再次查找。這在數據格式混亂或共享文件中數據錄入標準不統一的情況下尤其有用。

輸入上述任何公式後，如果您需要應用於多個查找值，請記住將公式複製到相鄰單元格——只需選擇該單元格，拖動填充柄向下，或根據需要使用 Ctrl+C 和 Ctrl+V。對於大表格，使用這些公式有助於確保可靠的匹配，而不改變原始數據庫。

此方法為大多數基於工作表的查找提供了靈活且普遍適用的解決方案。然而，對於非常大的數據集或當您需要自動處理大量記錄時，您可能會考慮使用像 VBA 這樣的自動化工具，以實現更高的效率。

使用 Kutools for Excel 快速修復格式不匹配問題

如果您偏好更快、無公式的解決方案，Kutools for Excel 提供了一個名為「文字與數值之間的轉換」的用戶友好工具。此功能允許您只需幾次點擊即可將以文字形式存儲的數字轉換為真正的數字——反之亦然。在執行 VLOOKUP 或 MATCH 等查找之前解決格式問題時特別有用。

安裝 Kutools for Excel 後，請按照以下步驟操作。

1. 選擇包含問題數據的範圍（例如，以文字形式存儲的數字）。
2. 前往「Kutools」>「文字」>「文字與數值之間的轉換」。
3. 在彈出的對話框中：
   1. 如果由於以文字格式存儲的數字導致查找失敗，請選擇「文字至數值」。
      （或者，如果查找值是以文字形式存儲，請選擇「數值至文字」。）
   2. 點擊「確定」立即轉換數據格式。

      

將文字轉換為數字後，轉換後的單元格將作為真正的數字行為，不再顯示表示不一致的綠色三角形指示器。

此方法消除了輔助列、公式或 VBA 的需求，使其成為在應用 VLOOKUP 之前快速清理的理想選擇。

VBA 宏：在 VLOOKUP 前標準化格式

對於經常處理大型數據集、接收外部來源文件或需要重複自動化的用戶，使用簡單的 VBA 宏可以程序化地標準化查找值列和查找表列的數據格式。這樣，您可以在運行 VLOOKUP 之前確保所有數據都已轉換為文字或數字，消除因格式不匹配導致的匹配錯誤。VBA 特別適用於批量處理，節省手動調整，並通過自動化確保數據一致性。

優勢： 自動化大型數據範圍或頻繁工作流程的格式化；最小化遺漏或格式不一致的風險；適用於重複性任務。
劣勢： 不適合有宏限制或不熟悉 VBA 宏使用的用戶。

以下是使用宏標準化單元格格式的方法：

1. 轉到開發工具選項卡，點擊 Visual Basic 打開 VBA 編輯器。在新窗口中，點擊 插入 > 模塊，然後將以下代碼複製並粘貼到模塊區域：

Sub StandardizeLookupFormats()
    ' Ask the user to select the lookup column and choose a target format
    Dim rng As Range
    Dim userChoice As Integer
    Dim xTitleId As String
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set rng = Application.InputBox("Select the range to standardize (lookup or data column):", xTitleId, Type:=8)
    If rng Is Nothing Then Exit Sub
    
    userChoice = MsgBox("Convert selected data to Number? (Click Yes to convert to Number, No to convert to Text)", vbYesNoCancel, xTitleId)
    
    If userChoice = vbYes Then
        For Each cell In rng
            If IsNumeric(cell.Value) Then
                cell.Value = Val(cell.Value)
                cell.NumberFormat = "General"
            End If
        Next
    ElseIf userChoice = vbNo Then
        For Each cell In rng
            If Not IsEmpty(cell.Value) Then
                cell.Value = CStr(cell.Value)
                cell.NumberFormat = "@"
            End If
        Next
    Else
        Exit Sub
    End If
End Sub

2. 關閉 VBA 編輯器。要運行宏，返回 Excel，按下 Alt+F8，選擇 StandardizeLookupFormats，然後點擊 運行。

操作詳細信息與提示：

• 此宏將提示您選擇要標準化的列（您的 LOOKUP 範圍或 TABLE 範圍）。
• 選擇之後，它將詢問您是否要將範圍轉換為數字（點擊 是）或文字（點擊 否）。為您的查找和表格列選擇相同的格式，以確保 VLOOKUP 能夠可靠地匹配。
• 運行此宏後，如果結果未立即顯示，您可能需要重新計算工作表（按 F9）或重新應用您的 VLOOKUP 公式。
• 如果您收到宏被禁用的錯誤，請在繼續之前啟用 Excel 設置中的宏。

此解決方案非常適合重複數據導入，或在應用 VLOOKUP 或其他查找操作之前清理大型數據集中不一致的列。

其他內置的 Excel 方法：使用「文本轉欄」修復數據格式

在 Excel 中對齊數字和文本格式的一個快速方法是使用「文本轉欄」功能。這個內置工具通常用於拆分數據，但也可以在不編輯公式的情況下強制進行格式轉換，當您想要一次性修復或處理簡單列表時非常有用。

優點：非常容易，不需要公式或代碼，保留原始數據結構；缺點：適合一次性修正，如果數據更改，不會自動更新。

要使用此方法將列中的數字（或反之亦然）轉換為文字存儲：

• 選擇具有疑似格式不匹配的列（例如，您的查找列或由 VLOOKUP 引用的列）。
• 在數據選項卡上，點擊 文本轉欄。
• 在向導中，選擇 分隔符，然後點擊 下一步。
• 取消選擇所有分隔符複選框（因為您並未拆分數據）；點擊 下一步。
• 在列數據格式中，選擇 常規（以強制 Excel 將數字識別為數字），或選擇 文本（將數字轉換為文字）。
• 點擊 完成 以完成過程。

完成後，您的數據將被強制對齊為數字或文本格式，解決 VLOOKUP 不匹配問題。始終檢查一些單元格以確認轉換是否如預期進行。如有必要，針對查找列和查找值重複此過程以最大程度提高一致性。

實用提醒：「文本轉欄」直接修改數據，因此如果右側已有數據，可能會覆蓋單元格內容。如果不確定，請先將列複製到空白區域，並在使用批量數據工具之前始終備份文件。

最佳 Office 生產力工具

🤖	Kutools AI 助手：以智能執行為基礎，革新數據分析 ｜生成程式碼 ｜ 創建自訂公式 ｜ 分析數據並生成圖表 ｜ 調用 Kutools 增強函數…
	熱門功能：查找、選取項目的背景色或標記重複值 ｜ 刪除空行 ｜ 合併列或單元格且不遺失數據 ｜ 四捨五入（免公式）...
	高級 LOOKUP：多條件 VLookup｜多值 VLookup｜多表查找｜模糊查找...
	高級下拉列表：快速創建下拉列表 ｜依賴型下拉列表 ｜ 多選下拉列表...
	列管理器：添加指定數量的列 ｜ 移動列 ｜ 切換隱藏列的顯示狀態 ｜ 比較區域及列...
	精選功能：網格聚焦 ｜ 設計檢視 ｜ 增強編輯欄 ｜ 工作簿及工作表管理器 ｜ 資源庫（快捷文本） ｜ 日期提取器 ｜ 合併資料 ｜ 加密/解密儲存格 ｜ 按列表發送電子郵件 ｜ 超級篩選 ｜ 特殊篩選（篩選粗體/傾斜/刪除線...）...
	15 大工具集：12 項文本工具（添加文本、刪除特定字符…）｜50+ 儀表 類型（甘特圖等）｜40+ 實用 公式（基於生日計算年齡等）｜19 項插入工具（插入QR码、根據路徑插入圖片等）｜12 項轉換工具（金額轉大寫、匯率轉換等）｜7 項合併與分割工具（高級合併行、分割儲存格等）｜...及更多

使用 Kutools，語言任你選 — 支援英語、西班牙語、德語、法語、中文及超過40 種語言！

運用 Kutools for Excel，全面提升您的 Excel 技能，體驗前所未有的高效。 Kutools for Excel 提供超過300 項進階功能，讓您提升工作效率、節省時間。 點此尋找您最需要的功能...

查看更多 Kutools for Excel 詳情... 免費下載...

---

Office Tab 為 Office 帶來分頁介面，讓您的工作更加輕鬆簡單

• 在 Word、Excel、PowerPoint 中啟用分頁編輯與閱讀。
• 在同一視窗的新分頁中打開與創建多份文件，而非開啟新視窗。
• 提升您的生產力50%，每日可幫您減少數百次鼠標點擊！

查看更多 Office Tab 詳情... 免費下載...

---

所有 Kutools 外掛，一次安裝

Kutools for Office 套裝整合了 Excel、Word、Outlook 和 PowerPoint 的外掛，外加 Office Tab Pro，非常適合需要跨 Office 應用程式協同作業的團隊。

• 全合一套裝 — Excel、Word、Outlook及 PowerPoint 外掛 + Office Tab Pro
• 一鍵安裝，一份授權 — 幾分鐘完成設置（支援 MSI）
• 協同運作更順暢 — Office 應用間無縫提升生產力
• 30 天全功能試用 — 無需註冊、無需信用卡
• 最超值 — 一次購買，節省單獨外掛費用

查看更多 Kutools for Office 詳情... 免費下載...