這裡將介紹 Excel VBA 中各種字串的操作方式與相關的功能函數，並且提供各式常見的使用範例程式碼。

在使用 Excel VBA 處理資料時，通常除了數值的資料之外，文字的資料處理也是很常會遇到的工作，而相較於運算很單純的數值，文字的處理又更複雜了一些，例如轉換大小寫、取出部分的文字、比較或取代文字等，通常文字在處理時，都需要根據不同的問題來撰寫 VBA 程式碼，以下我們會說明各種常見的字串處理函數，並且提供各種常見的範例程式給大家參考。

字串的宣告與定義

VBA 中的 String 就是用來儲存字串（文字）的變數類型，其宣告與定義的方式跟一般的數值變數類似，而字串的內容在指定時要用雙引號包起來，例如：

Dim mystr As String
mystr = "This is a message!!!"

上面這兩行宣告了一個 mystr 字串變數，並且把該變數的內容設定為 This is a message!!!。

另外我們也可以利用 String 函數來建立重複字元的字串，例如：

Dim text1, text2 As String
text1 = String(6, "A") ' 產生 AAAAAA
text2 = String(3, 100) ' 產生 ddd

String 第一個參數是指定要產生的字串長度，而第二個參數則是指定重複的字元，除了直接用雙引號包起來的方式指定字元之外，也可以直接用 ASCII 的編碼指定，像這裡的 100 就是指 d 的 ASCII 編碼。

連接字串

如果要將多個字串連接起來，可以使用 & 這個運算子，以下是將三個字串連接在一起的範例。

Dim text1 As String, text2 As String
text1 = "Hello"
text2 = "World"

' 使用 & 連接字串
MsgBox text1 & ", " & text2

這裡我們定義了兩個字串變數 text1 與 text2，接著使用 & 把它們接在一起，並且在兩個英文單字之間加入一個逗號與空白。

VBA 連接字串

執行之後，就會輸出三個字串連接起來的結果。

VBA 輸出訊息

字串長度

在 VBA 中若要取得一個字串的長度，可以使用 Len 函數：

MsgBox Len("Hello, world.")

Len 函數的參數只有一個字串變數，呼叫 Len 之後它就會傳回該字串的長度：

VBA 取得字串長度

VBA 取得字串長度

Len 傳回的數值是一個整數，我們也可以把它儲存在變數中做其他的運算：

Dim text As String
text = "Hello World"

Dim textLen As Integer
textLen = Len(text)

MsgBox "textLen = " & textLen

VBA 取得字串長度

VBA 取得字串長度

搜尋字串位置

若要尋找一個字串中某個子字串的位置，可以使用 InStr 函數：

Dim pos As Integer
pos = InStr("Hello, world.", "world")
MsgBox "pos = " & pos

InStr 第一個參數是放一個比較長的字串，而第二個參數則是放要搜尋的關鍵字，以這個例子來說它就會在 Hello, world. 這一個字串中尋找 world 出現的位置。

VBA 搜尋字串位置

VBA 搜尋字串位置

InStr 預設在搜尋時，英文的大小寫是視為不同的，若希望不分大小寫，可將比對方式參數設定為 vbTextCompare：

Dim pos As Integer
pos = InStr(1, "Hello, World.", "world", vbTextCompare)
MsgBox "pos = " & pos

這裡的第一個參數是用來指定搜尋的起始位置，在上面的範例中我們將這個參數省略掉了，在這裡使用比對方式參數時就要加進來，這樣執行之後 InStr 在比對字串時就會將英文字母的大小寫視為相同的。

在 Excel VBA 程式碼編輯器中輸入函數時，通常都會有這樣的參數用法提示。

VBA 參數用法提示

有些函數的參數非常多，若是忘記參數的詳細位置與順序時，就可以善用這個小技巧。

如果想從字串的結尾處開始往左搜尋，則可改用 InStrRev：

Dim pos As Integer
pos = InStrRev("Hello, World.", "l")
MsgBox "pos = " & pos

VBA 搜尋字串位置

若要讓 InStrRev 搜尋時不分大小寫，可以這樣寫：

Dim pos As Integer
pos = InStrRev("Hello, WORLD.", "l", -1, vbTextCompare)
MsgBox "pos = " & pos

第三個參數是搜尋起始點，預設值是 -1（從最右方開始搜尋），最後一個參數就是比對方式，用法同上。

取出子字串

如果要從一段字串中取出部分的子字串，依據不同的狀況會有好幾種作法，以下是各種取出子字串的函數與用法。

開頭子字串

如果想要取出一段文字中開頭的部分，可以使用 Left 函數：

MsgBox Left("Hello, world.", 5)

Left 的意思就是從左邊開始擷取子字串的意思，第一個參數是完整的字串，而第二個參數則是要擷取的子字串長度，執行結果如下：

VBA 取出開頭子字串

結尾子字串

如果想要取出一段文字中結尾的部分，可以使用 Right 函數，其用法跟 Left 類似，只是換成從右邊開始擷取子字串而已：

MsgBox Right("Hello, world.", 6)

執行結果如下：

VBA 取出結尾子字串

任意位置子字串

如果要擷取的子字串位置不在文字的兩端，而是在文字的中間，則可使用 Mid 函數：

MsgBox Mid("This is a message.", 6, 2)

Mid 函數的第一個參數是完整的字串，第二個參數是要擷取的子字串位置（從左邊算起），而第三個參數則是要擷取的子字串長度，這個例子就會從 This is a message. 這段文字的第 6 個字元開始擷取，取出 2 個字元，執行結果如下。

VBA 取出任意位置子字串

如果使用 Mid 時不指定擷取的字串長度，則 Mid 就會從指定的位置開始擷取直到整個字串的結尾，例如：

MsgBox Mid("This is a message.", 6)

VBA 取出任意位置子字串

移除多餘的空白字元

如果在字串中除了主要的文字資料之外，前方還包含了多餘的空白字元，這時候就可以使用 Trim 系列的函數將多餘的空白移除。

移除開頭空白字元

LTrim 函數可將字串左邊的空白移除：

Dim mystr As String
mystr = "         Hello, world."
MsgBox "After LTrim : " & LTrim(mystr)

LTrim 會自動判斷字串開頭的空白字元長度，將左邊開頭所有的空白都刪除，執行結果為：

VBA 移除空白字元

移除結尾空白字元

如果空白位於字串結尾處，則可使用 RTrim，其用法與 LTrim 類似：

Dim mystr As String
mystr = "Hello, world.              "
MsgBox "After RTrim : " & RTrim(mystr)

結尾的空白移除之後，執行結果與上面相同。

移除開頭與結尾空白字元

如果要同時將前後兩端的所有空白字元都刪除，可以使用 Trim 函數：

Dim mystr As String
mystr = "       Hello, world.        "
MsgBox "After Trim : " & Trim(mystr)

開頭與結尾的空白移除之後，執行結果與上面相同。

產生空白字元

如果需要特定長度的空白字串時，可以用 Space 來產生，例如：

MsgBox ("Hello," & Space(10) & "world.")

VBA 產生空白字元

字串取代

Replace 函數可以將字串中的指定的文字替換成其他的文字，這個函數的完整參數用法如下：

Replace(字串, 搜尋文字, 替換文字[, 起始位置[, 替換次數[, 比對方式]]])

最簡單的用法就是單純將字串中指定的文字替換掉：

Dim mystr As String
mystr = "This is a message."
newstr = Replace(mystr, "message", "dog")
MsgBox "After Replace : " & newstr

這裡是將 mystr 中的 message 替換為 dog，結果如下：

VBA 字串取代

如果指定要替換的文字在整個字串中有出現好多次，Replace 預設會全部替換掉：

Dim mystr As String
mystr = "This is a message."
newstr = Replace(mystr, "is", "**")
MsgBox "After Replace : " & newstr

這裡就會將字串中的兩個 is 都替換為 **，結果如下：

VBA 字串取代

如果只想要替換特定位置的文字，或是限制替換次數，可以搭配起始位置與替換次數兩個參數的方式來處理：

Dim mystr As String
mystr = "This is a message."
newstr = Replace(mystr, "is", "**", 1, 1)
MsgBox "After Replace : " & newstr

這樣 Replace 就會從字串的開頭開始搜尋，只替換第一個比對成功的文字。

VBA 字串取代

如果要替換比較後面的文字，可以調整起始位置參數，不過 Replace 會自動將起始位置之前的文字截斷：

Dim mystr As String
mystr = "This is a message."
newstr = Replace(mystr, "is", "**", 5, 1)
MsgBox "After Replace : " & newstr

執行結果如下：

VBA 字串取代

在預設的狀況下 Replace 會將英文字母的大小寫視為不同的字串：

Dim mystr As String
mystr = "This Is a Message."
newstr = Replace(mystr, "is", "**")
MsgBox "After Replace : " & newstr

VBA 字串取代

如果要讓 Replace 在比對時，不要區分英文字母的大小寫（大小寫視為相同），可以將比對方式參數指定為 vbTextCompare：

Dim mystr As String
mystr = "This Is a Message."
newstr = Replace(mystr, "is", "**", 1, -1, vbTextCompare)
MsgBox "After Replace : " & newstr

由於比對方式的參數是最後一個，所以在使用時要把前面的起始位置與替換次數兩個參數也都寫進去，這裡的替換次數設定為 -1 是代表不限制的意思，而比對方式預設值是 vbBinaryCompare（大小寫視為不同），這裡改為 vbTextCompare 之後，就可以同時比對字串中大寫與小寫的文字。

VBA 字串取代

字串比較

StrComp 可以比較不同字串之間的大小差異，在排序資料時常常會用到，其參數用法如下：

StrComp(字串一, 字串二[, 比對方式])

前兩個參數就是兩個要比較的字串，而第三個比對方式參數可用來指定是否區分大小寫，預設值是 vbBinaryCompare（大小寫視為不同），若設定為 vbTextCompare 則會將大小寫視為相同。

而 StrComp 在比較兩個字串之後，會傳回不同的數值來代表不同的結果：

狀況StrComp 傳回數值
字串一 < 字串二-1
字串一 = 字串二0
字串一 > 字串二1

以下是一些比較的範例：

MsgBox StrComp("Hello", "Hello") ' 結果為 0
MsgBox StrComp("Hello", "HELLO") ' 結果為 1
MsgBox StrComp("Hello", "hello") ' 結果為 -1
MsgBox StrComp("Hello", "hello", vbTextCompare) ' 結果為 0

字串反轉

StrReverse 可將字串反轉：

MsgBox StrReverse("Hello, world.")

VBA 字串反轉

大小寫轉換

UCase 與 LCase 可以將字串中的英文字母轉換為大寫或小寫：

MsgBox UCase("Hello, world.") ' HELLO, WORLD.
MsgBox LCase("Hello, world.") ' hello, world.

常見範例

從學號產生 Email 位址

假設我們在 Excel 檔有一系列的學號，而各個學生的 Email 位址都是依據學號來建立的。

VBA 以學號產生 Email

這樣我們就可以利用簡單的 VBA 程式將學號轉換成 Email 位址：

Dim sid, email As String
For i = 2 To 7
  sid = Cells(i, 1)
  email = sid & "@your.domain.com"
  Cells(i, 2) = email
Next i

執行的結果如下：

VBA 以學號產生 Email

從 Email 取出學號

假設我們有一些固定格式的 Email 位址，而這些 Email 都是以一個英文字母 u 開頭，加上學生的學號組成的，實際資料如下：

VBA 從 Email 取出學號

若要從中取出學號的部分（也就是數字的部分），可以這樣寫：

Dim sid, email As String
For i = 2 To 7
  email = Cells(i, 1)
  sid = Mid(email, 2, 7)
  Cells(i, 2) = sid
Next i

執行的結果如下：

房屋實價登錄資料

這是從政府資料開放平台上下載的房屋實價登錄資料。

房屋實價登錄資料

假設我要找出所有大安區信義路上房屋的平均單價，就可以使用一個 For 迴圈配合 InStr 來搜尋：

Dim street, price As String
Dim s As Double
Dim cnt As Integer
s = 0
cnt = 0
For i = 2 To 948
  street = Cells(i, 2) ' 門牌

  ' 檢查門牌是否包含「大安區信義路」
  If InStr(street, "大安區信義路") >  Then

    price = Cells(i, 5)    ' 單價
    If Len(price) > 0 Then ' 跳過沒有單價的資料
      cnt = cnt + 1        ' 計算建物總數
      s = s + Val(price)   ' 計算建物單價總和
    End If
  End If
Next i
MsgBox "建物總數 = " & cnt & vbCrLf _
  & "平均單價 = " & Round(s / cnt)

這裡我們以一個 For 迴圈把每一筆房屋成交紀錄都跑過一次，把每一筆資料的門牌地址抓出來使用 InStr 比對，如果有包含大安區信義路 的字樣，就把該建物的單價記錄下來，最後即可求得整體的平均單價。

由於有些資料的單價欄位是空白的，所以我們在使用單價欄位資料之前還要先以 Len 來檢查一下，確認這個欄位確實有資料之後，才將該筆資料納入計算。

這裡的 Val 函數的用途是將字串資料轉換為數值，而輸出時所加入的 vbCrLf 是指換行的意思。

房屋實價登錄資料

這個範例的資料與 VBA 程式我放在這個 Excel 檔，有興趣的人可以下載回去執行。

這裡我們只是拿房屋實價登錄資料來示範字串處理的應用方式，實際上房屋的價格評估當然沒有那麼單純，真正的重點在於熟練各種字串處理的相關函數，並結合各種迴圈與判斷結構，練習針對類型的文字資料撰寫對應的 VBA 程式碼。

參考資料