壹般的函數很難完成這個替換,需要用VBA 來執行,利用字典和替換字符函數來完成,代碼很簡單,如下:
Sub 替換()
Dim Dic As Variant
n = Cells(Rows.Count, 2).End(xlUp).Row
Set Dic = CreateObject("Scripting.Dictionary")
For i = 2 To n
Dic(Cells(i, 2).Value) = "中國"
Next
arr = Dic.keys
n = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To n
For j = 0 To UBound(arr)
If Application.IsError(Application.Find(arr(j), Cells(i, 1))) = False Then
Cells(i, "a") = Application.Substitute(Cells(i, 1), arr(j), "中國")
Exit For
End If
Next
Next
End Sub