Anne-Mie nhận ra rằng cô ấy có thể sử dụng ký tự đại diện (?) Để tìm kiếm trong Excel, nhưng cô ấy tự hỏi liệu mình có thể sử dụng ký tự đại diện trong chuỗi thay thế hay không. Ví dụ: cô ấy muốn tìm kiếm “abde” và thay thế nó bằng “aa * de”, trong đó dấu hoa thị đại diện cho bất kỳ số ký tự nào hoặc không có ký tự nào cả.

Câu trả lời ngắn gọn là không có cách nào để thực hiện điều này trong Excel, như được mô tả. Nếu bạn chỉ muốn chuyển đổi ký tự thứ hai của giá trị văn bản từ “b” thành “a”, thì điều đó có thể được thực hiện khá dễ dàng:

=REPLACE(A1,2,1,"a")

Tuy nhiên, điều này có lẽ không phải là điều bạn muốn làm; bạn muốn sử dụng ký tự đại diện trong văn bản “thay thế bằng”. Thuật ngữ kỹ thuật để thực hiện các thay thế chuỗi như vậy được gọi là REGEX, viết tắt của Regular Expressions. REGEX bắt đầu với các ngôn ngữ như Perl nhưng mạnh đến mức nhiều ngôn ngữ lập trình khác đã thêm nó vào.

VBA được sử dụng trong Excel cũng không ngoại lệ. REGEX đã được thêm vào Visual Basic 6.0, có nghĩa là nó đã chuyển sang VBA của Excel trong Excel 2003. Bước đầu tiên khi sử dụng REGEX là bật nó lên. Bạn thực hiện việc này trong VBA Editor bằng cách chọn Công cụ | Tham khảo và sau đó đảm bảo có dấu kiểm bên cạnh tùy chọn Microsoft VBScript Regular Expressions 5.5.

Bật tham chiếu này cho phép bạn tạo các đối tượng REGEX. Các đối tượng này có thuộc tính Test method và Pattern. Điều này có nghĩa là bạn đặt thuộc tính Mẫu và sau đó Phương pháp kiểm tra sẽ kiểm tra xem mẫu có tồn tại hay không. Một đối tượng REGEX cũng có một phương thức Replace, được sử dụng để thay thế.

Trước khi tiếp tục, điều quan trọng là phải hiểu rằng các biểu thức chính quy có thể rất phức tạp và “geeky”. Không có cách nào xung quanh nó; cách làm việc với biểu thức chính quy đã là chủ đề của toàn bộ cuốn sách. May mắn thay, đối với mục đích của mẹo này, các biểu thức về bản chất khá đơn giản. Trong trường hợp này, chúng tôi sẽ sử dụng mẫu “^ ab.de $”. Mẫu này đề cập đến một từ bắt đầu (được biểu thị bằng ^) với “ab” theo sau là một biểu thức tùy ý (được biểu thị bằng)

bao gồm ít nhất một ký tự (được biểu thị bằng dấu chấm) và kết thúc (được biểu thị bằng $) bằng “de”.

Đây là đoạn mã triển khai việc sử dụng đối tượng REGEX để thực hiện các thay thế thực tế.

Public Function SearchNReplace1(Pattern1 As String, _   Pattern2 As String, Replacestring As String, _   TestString As String)

Dim reg As New RegExp

reg.IgnoreCase = True     reg.MultiLine = False     reg.Pattern = Pattern1     If reg.Test(TestString) Then         reg.Pattern = Pattern2         SearchNReplace = reg.Replace(TestString, ReplaceString)

Else         SearchNReplace = TestString     End If End Function

Để sử dụng macro này, hãy bắt đầu với các chuỗi bạn muốn thay đổi trong cột A. Giả sử rằng chuỗi đầu tiên nằm trong ô A1, bạn có thể đặt chuỗi sau vào một ô khác để nhận văn bản đã thay đổi:

=SearchNReplace1("^ab.*de$","^ab","aa",A1)

Điều này cho macro biết rằng mẫu bạn muốn tìm là “^ ab. * De $”

(tham số đầu tiên) và bạn muốn thay thế “^ ab” bằng “aa”.

Công thức này có thể được dán xuống cột và bạn sẽ kết thúc bằng một chuyển đổi cột A trong đó chuỗi “abde” được thay thế bằng “aade”.

Nếu bạn đang sử dụng phiên bản Excel cũ hơn không cho phép bạn tạo các đối tượng REGEX hoặc nếu bạn không muốn làm như vậy, thì bạn có thể tạo macro chỉ cần bước qua một nhóm ô đã chọn và tìm ô bất kỳ. bắt đầu bằng “ab” và kết thúc bằng “de”, sau đó thay thế phần đầu bằng “aa”.

Sub SearchNReplace2()

Dim sFindInitial As String     Dim sReplaceInitial As String     Dim iLenInitial As Integer     Dim sFindFinal As String     Dim sReplaceFinal As String     Dim iLenFinal As Integer     Dim sTemp As String     Dim rCell As Range

sFindInitial = "ab"

sReplaceInitial = "aa"

sFindFinal = "de"

sReplaceFinal = "de"



For Each rCell In Selection         sTemp = rCell.Value         iLenInitial = Len(sFindInitial)

iLenFinal = Len(sFindFinal)

If Left(sTemp, iLenInitial) = sFindInitial And _             Right(sTemp, iLenFinal) = sFindFinal Then             sTemp = Mid(sTemp, iLenInitial + 1)

sTemp = Left(sTemp, Len(sTemp) - iLenFinal)

sTemp = sReplaceInitial & sTemp & sReplaceFinal             rCell.Value = sTemp         End If     Next     Set rCell = Nothing End Sub

Để sử dụng quy trình này, chỉ cần chọn các ô bạn muốn thay đổi, sau đó thực thi macro. Bạn cũng nên thực hiện các thay đổi đối với các biến sFindInitial, sReplaceInitial, sFindFinal và sReplaceFinal, nếu cần.

_Lưu ý: _

Nếu bạn muốn biết cách sử dụng các macro được mô tả trên trang này (hoặc trên bất kỳ trang nào khác trên trang ExcelTips), tôi đã chuẩn bị một trang đặc biệt bao gồm thông tin hữu ích.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (3303) áp dụng cho Microsoft Excel 97, 2000, 2002 và 2003. Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện ribbon của Excel (Excel 2007 trở lên) tại đây: