Tom có ​​một trang tính chứa khoảng 20.000 ô đầy đủ dữ liệu dạng văn bản. Từ bên trong các ô này, anh ta cần trích xuất một mẫu văn bản cụ thể. Mẫu là – # trong đó mỗi # là một chữ số. Mẫu này không xuất hiện tại một vị trí đã định trong mỗi ô. Tom tự hỏi liệu có cách nào để trích xuất thông tin mong muốn không.

Có một số cách mà bạn có thể tiếp cận vấn đề này và giải pháp chính xác cho nhu cầu của bạn sẽ phụ thuộc vào đặc điểm của dữ liệu mà bạn đang làm việc. Nếu bạn biết rằng vị trí duy nhất trong dữ liệu của bạn mà bạn sẽ có dấu gạch ngang nằm trong mẫu của bạn, thì bạn có thể loại bỏ sự hiện diện của dấu gạch ngang bằng cách sử dụng công thức như sau:

=MID(A1,FIND("-",A1)-2,8)

Thao tác này sẽ tìm dấu gạch ngang và sau đó lấy hai ký tự ở bên trái của dấu gạch ngang. Điều này rõ ràng sẽ không hoạt động nếu có dấu gạch ngang ở những nơi khác trong văn bản hoặc nếu có thể có “mẫu” bao gồm các chữ số không (chẳng hạn như 12-34B32) và bạn muốn loại trừ chúng. Trong trường hợp đó, bạn sẽ cần một công thức phức tạp hơn nhiều:

=IF(ISERROR(INT(MID(A1, FIND("-", A1, 1)-2, 2)) & INT(MID( A1, FIND("-", A1, 1)+1, 5))), "", MID(A1, FIND("-", A1)-2, 8))

Điều này bao gồm một thành phần kiểm tra lỗi để tìm xem liệu các ký tự ngay trước dấu gạch ngang và ngay sau dấu gạch ngang có chứa bất kỳ thứ gì khác ngoài chữ số hay không. Nếu họ làm vậy, thì không có gì được trả lại.

Một điều mà các phương pháp tiếp cận công thức này không làm được là xử lý những tình huống có thể có nhiều hơn một lần xuất hiện của mẫu trong cùng một ô. Trong trường hợp đó, macro là cách tiếp cận tốt nhất. Phần sau sẽ trích xuất các mẫu hợp lệ và đặt chúng vào một trang tính mới được gọi là “Kết quả”.

Sub ExtractPattern()

On Error Resume Next     Set SourceSheet = ActiveSheet     Set TargetSheet = ActiveWorkbook.Sheets("Results")

If Err = 0 Then         Worksheets("Results").Delete     End If     Worksheets.Add     ActiveSheet.Name = "Results"

Set TargetSheet = ActiveSheet     Cells(1, 1).Value = "Found Codes"

Cells(1, 1).Font.Bold = True     iTargetRow = 2

SourceSheet.Select     Selection.SpecialCells(xlCellTypeLastCell).Select     Range(Selection, Cells(1)).Select

For Each c In Selection.Cells         If c.Value Like "##-#####" Then             sRaw = c.Value             iPos = InStr(sRaw, "-")

Do While iPos > 0                 If iPos < 3 Then                     sRaw = "  " & sRaw                     iPos = iPos + 2                 End If                 sTemp = Mid(sRaw, iPos - 2, 8)

sRaw = Mid(sRaw, iPos + 6, Len(sRaw))

If sTemp Like "##-#####" Then                     TargetSheet.Cells(iTargetRow, 1) = sTemp                     iTargetRow = iTargetRow + 1                 Else                     sRaw = Mid(sTemp, 4, 5) & sRaw                 End If                 iPos = InStr(sRaw, "-")

Loop         End If     Next c End Sub

Lưu ý rằng macro sử dụng chức năng Thích ở hai nơi. Phiên bản đầu tiên xác định xem mẫu có xảy ra ở bất kỳ vị trí nào trong ô hay không và trường hợp thứ hai được sử dụng để xác định xem các ký tự được trích xuất có khớp chính xác với mẫu mong muốn hay không.

_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 (12332) áp dụng cho Microsoft Excel 2007, 2010, 2013 và 2016.

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: