Contents ...
udn網路城邦
精選
[Excel VBA] 大量字串取代的做法
2009/04/02 12:01
瀏覽38,761
迴響2
推薦1
引用0

(本篇圖文及程式於2022年2月更新)

2009年應朋友要求寫了 Word VBA 利用Find/Replace功能做大量字串的取代（參見「如何用find/replace做大量不同字串的取代？」)，後來再寫了這一篇示範 Excel VBA的寫法。最近有網友來信詢問本文程式執行時的問題，才發現貼在部落格裡的程式碼貼上之後被弄亂（UDN部落格有這個問題），於是利用休假空閒時間，將程式稍為改寫，圖文也都重做。

修改文章

記得那位朋友說，需要把一些 Word 文件檔裡面的 "第一章" 全部改成  "第1章"、 "第一款" 全部改成  "第1款"、......諸如此類，字串非常多，最多會到第一百多章、第一百多款 (可能是討論法規) 。使用 Excel 實務會遇到的情況，例如：為了資料一致性，需要把地址資料裡的 "1段"、"2段" ...... 改成 "一段"、"二段" ... ...。

要做大量取代的地址資料

我們可以使用 Windows 的 " 記事本"(有 64KB 的限制) 或 "Wordpad"，建立一個取代字串的對照文字檔，另存新檔，編碼選擇 ANSI (副檔名 txt )，像這樣：

然後按 Alt+F11 進入 VBA 編輯狀態，在這個活頁簿裡面 "插入"-> "模組"，Excel 會將它命名為module1，把本篇文章稍後將列出來的程式碼 "複製-貼上" 到 module1 裡面。

再按一次 Alt+F11 回到 Excel 工作表畫面，按 Alt+F8，選擇 MassReplace 巨集，"執行" 它：

執行MassReplace 巨集

這個巨集程式首先會顯示對話視窗，讓我們選擇取代字串的對照文字檔，此時就要選擇剛

才建立的文字檔：

選擇要開啟的文字檔案

按下 "開啟"，片刻後，就可以看到檔案裡面字串取代的結果。

底下是把股號改成股名的例子：

[程式碼(文字)]

Option Base 0
Option Explicit
Sub MassReplace()
Dim arrStr() As String, InputStr As String
Dim sFilePath As Variant
Dim Fn As Integer
Fn = FreeFile() 取得一個可使用的檔案代號 (File Handle Number)
  顯示Windows的選擇檔案的對話窗
sFilePath = Application.GetOpenFilename("Text Files (*.txt), *.txt", 1, "選擇文字檔案", "開啟", False)
On Error Resume Next
If sFilePath Then                   若文字檔選擇成功
    Open sFilePath For Input As #Fn 開啟 Replace.txt 檔
    Application.ScreenUpdating = False 畫面暫停更新
    
    While Not EOF(Fn)
        Line Input #Fn, InputStr  從檔案讀出一列,
        If Len(InputStr) > 0 Then 略過無字串的空行
            arrStr = Split(InputStr, ",") 把讀入的文字列依逗號分成兩個字串, 置於 arrStr 陣列裡
            Call ReplaceText(arrStr(0), arrStr(1)) 叫用 ReplaceText函式, 把兩個字串傳給它執行取代
        End If
    Wend
    
    Application.ScreenUpdating = True 畫面恢復更新
    Close #Fn  關閉文字檔
Else
     MsgBox ("檔案選擇取消或失敗，將不執行文字取代。")
End If
End Sub
Function ReplaceText(Src As String, Rpl As String)
這個函式會在整個工作表裡搜尋 Src 字串, 將它取代為 Rpl 字串
Cells.Replace What:=Src, Replacement:=Rpl, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
              SearchFormat:=False, ReplaceFormat:=False
底下是 Replace 的參數說明： What 必選的參數。要尋找的字串。
Replacement 必選的參數。要置換的字串。
LookAt 選擇性的參數。是否需全字相同。可為下列 XlLookAt 常數之一：xlWhole 或 xlPart。
SearchOrder 選擇性的參數。搜尋的順序。可為下列 XlSearchOrder 常數之一：xlByRows 或 xlByColumns。
MatchCase 選擇性的參數。若指定為 True，則搜尋時大小寫視為相異。
SearchFormat 選擇性的參數。是否依據 "格式" 搜尋。True/False布林值。(Mac版無此參數)
ReplaceFormat 選擇性的參數。是否取代格式。True/False布林值。(Mac版無此參數)
End Function

[程式碼(截圖)]

*若要索取本文的 VBA 程式碼，來信主旨請寫：「Excel 大量字串取代 VBA」
  email 請寄way2cheng@gmail.com
*相關文章：
[Word VBA]如何用findeplace做大量不同字串的取代?

有誰推薦more
迴響（2） ：
2樓. betty
2010/09/30 17:12
請幫忙

大大想問一下

為什麼我都key進去了,但出現找到到指名引數?

Function ReplaceText(Src As String, Rpl As String)

你的程式裡可能有Key錯, 請檢查

Call Replace(.......) 這一行括弧裡的東西有沒有key錯

若仍找不出問題, 請把你 key 的程式碼 copy & paste 到

email 裡寄給我:

way_kkiimmoo@yahoo.com.tw
ThisIsTheWay 於 2010/10/04 08:37回覆
1樓. 電腦搞不定
2009/08/28 15:28
如何改寫模組!

您好:

我在工作上正傷透腦筋後求助很多周遭的人,經由介紹您的網頁教學,得知有"大量字串取代的做法"

測試成功,非常好用,但是有個問題請教你,想讓"取代"功能更理想

Excel裡如果不要整個工作表做字串取代,我只要其中儲存格F行,由上至最下取代

如何改寫模組呢? 謝謝!

還有我碰到更複雜的難題就是...我能傳E-maul Excel檔範例給你看嗎? 謝謝!

在程式裡先選取 F 欄:

Columns("F:F").Select

然後才 call ReplaceText()

在 ReplaceText 裡，把 Cells.Replace ......... 改成 Selection.Replace ..........

就會只針對 F 欄做取代。

作家簡介裡有我的email信箱，只是我近來事情很多很忙，身體很累，

所以會較慢回覆，先請原諒。
ThisIsTheWay 於 2009/08/30 20:51回覆
發表迴響

會員登入