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ệ. 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 Biểu thức chính quy Microsoft VBScript. Nếu có nhiều phiên bản tham chiếu, hãy chọn phiên bản mới nhất.

Bật tham chiếu này cho phép bạn tạo các đối tượng REGEX. Về mặt kỹ thuật, bạn có thể tắt nó đi, tùy thuộc vào cách bạn muốn liên kết xảy ra trong macro của mình. Một cuộc thảo luận về ưu và nhược điểm của muộn so với

Tuy nhiên, ràng buộc sớm nằm ngoài phạm vi của mẹo này, vì vậy chỉ cần tiếp tục và bật tham chiếu REGEX.

Các đối tượng REGEX sở hữu một phương pháp Kiểm tra và một thuộc tính Mẫu. Đ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         SearchNReplace1 = reg.Replace(TestString, ReplaceString)

Else         SearchNReplace1 = 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 không muốn sử dụng biểu thức REGEX trong VBA vì lý do nào đó, thì bạn có thể tạo một macro chỉ cần bước qua một nhóm các ô đã chọn và tìm kiếm bất kỳ ô nào bắt đầu bằng “ab” và kết thúc bằng “de”, và 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 (11170) áp dụng cho Microsoft Excel 2007, 2010, 2013, 2016, 2019 và Excel trong Office 365. Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện menu cũ hơn của Excel tại đây: