Xin tư vấn công thức loại bỏ các ký tự đặc biệt trong chuỗi, chỉ giữ lại các ký tự từ A-Z, các con số và khoảng trắng Char32

[tieutuvodanh192]

Chào tất cả các ACE trên diễn đàn GPE
Hiện tại em có 1 cột dữ liệu có chứa các ký tự đặc biệt xen kẽ trong đó, em đang sử dụng hàm SUBSTITUTE để loại bỏ các ký tự mà em có thể tìm thấy, với cách làm này thì hàm của em khá cồng kềnh và không thể phát hiện các ký tự đặc biệt khác nếu có phát sinh thêm.
Nhờ các ACE xem giúp em có thể áp dụng công thức nào để có thể loại bỏ các ký tự đặc biệt trong chuỗi, chỉ giữ lại các ký tự từ A-Z, khoảng trắng Char32 và các con số một cách hiệu quả nhất.
File mẫu như ở file đính kèm. Em cảm ơn.

 

[snow25]

Chào tất cả các ACE trên diễn đàn GPE
Hiện tại em có 1 cột dữ liệu có chứa các ký tự đặc biệt xen kẽ trong đó, em đang sử dụng hàm SUBSTITUTE để loại bỏ các ký tự mà em có thể tìm thấy, với cách làm này thì hàm của em khá cồng kềnh và không thể phát hiện các ký tự đặc biệt khác nếu có phát sinh thêm.
Nhờ các ACE xem giúp em có thể áp dụng công thức nào để có thể loại bỏ các ký tự đặc biệt trong chuỗi, chỉ giữ lại các ký tự từ A-Z và khoảng trắng Char32 một cách hiệu quả nhất.
File mẫu như ở file đính kèm. Em cảm ơn.

Chạy thử cái code này.

Sub laykytu()
   Dim arr, i As Long, lr As Long, kq(), j As Long, a As Long
   With Sheet1
        lr = .Range("A" & Rows.Count).End(xlUp).Row
        arr = .Range("A2:A" & lr).Value
        ReDim kq(1 To UBound(arr), 1 To 1)
        For i = 1 To UBound(arr)
            For j = 1 To Len(arr(i, 1))
                a = AscW(Mid(arr(i, 1), j, 1))
                If a = 32 Or (a > 64 And a < 91) Then
                   kq(i, 1) = kq(i, 1) & Mid(arr(i, 1), j, 1)
                End If
             Next j
        Next i
        .Range("B2:B" & lr).Value = kq
   End With
End Sub

 

[tieutuvodanh192]

Chạy thử cái code này.

Sub laykytu()
   Dim arr, i As Long, lr As Long, kq(), j As Long, a As Long
   With Sheet1
        lr = .Range("A" & Rows.Count).End(xlUp).Row
        arr = .Range("A2:A" & lr).Value
        ReDim kq(1 To UBound(arr), 1 To 1)
        For i = 1 To UBound(arr)
            For j = 1 To Len(arr(i, 1))
                a = AscW(Mid(arr(i, 1), j, 1))
                If a = 32 Or (a > 64 And a < 91) Then
                   kq(i, 1) = kq(i, 1) & Mid(arr(i, 1), j, 1)
                End If
             Next j
        Next i
        .Range("B2:B" & lr).Value = kq
   End With
End Sub

Cảm ơn anh nhiều, trường hợp này nếu sử dụng VBA thì sẽ giải quyết rất tốt, tuy nhiên nếu chỉ thực hiện bằng công thức Excel đơn thuần thì có khả thi không anh.
(Trong bài viết em sơ sót quên mất là giữ lại thêm các con số, em đã chỉnh lại thông tin ở bài đăng)

 

[HDDP]

Bạn xem thử đúng ý chưa nhé.

 

[befaint]

[A-Z ]

=TEXTJOIN("",TRUE,IFERROR(IF(FIND(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ "),MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),""),""))

Excel 2019/ 365
Nhấn CSE

 

[tieutuvodanh192]

View attachment 274985
Bạn xem thử đúng ý chưa nhé.

[A-Z ]

=TEXTJOIN("",TRUE,IFERROR(IF(FIND(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ "),MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),""),""))

Excel 2019/ 365
Nhấn CSE

Excel máy em đang cài bản Standard 2016 nên không có những hàm này, tuy nhiên khi đọc công thức thì em cũng hiểu hướng để giải quyết vấn đề của mình.
Cảm ơn hai anh.

 

[VetMini]

[A-Z ]

=TEXTJOIN("",TRUE,IFERROR(IF(FIND(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ "),MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),""),""))

Excel 2019/ 365
Nhấn CSE

2019 không kể
Nhưng 2021 và 365 thì có thể dùng Let hay Lambda để tránh Indirect được hôn?

 

[befaint]

2019 không kể
Nhưng 2021 và 365 thì có thể dùng Let hay Lambda để tránh Indirect được hôn?

Let chỉ là gán biểu thức vào Name thôi anh.

Chỗ tạo dãy số đó dùng SEQUENCE() chứ anh.

=Let(listSplitChars, mid(A2,SEQUENCE(LEN(A2)),1), TEXTJOIN("",TRUE,IFERROR(IF(FIND(listSplitChars,"ABCDEFGHIJKLMNOPQRSTUVWXYZ 0123456789"), listSplitChars,""),"")))

 

[VetMini]

Let chỉ là gán biểu thức vào Name thôi anh.

Chỗ tạo dãy số đó dùng SEQUENCE() chứ anh.

=Let(listSplitChars, mid(A2,SEQUENCE(LEN(A2)),1), TEXTJOIN("",TRUE,IFERROR(IF(FIND(listSplitChars,"ABCDEFGHIJKLMNOPQRSTUVWXYZ 0123456789"), listSplitChars,""),"")))

Lambda mới là gán vào name chứ.
Let tương tự như code Function tại chỗ của JavaScript.

 

[befaint]

Lambda mới là gán vào name chứ.
Let tương tự như code Function tại chỗ của JavaScript.

Tài liệu họ viết là 'assigns names' mà anh.

https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999

 

[VetMini]

Tài liệu họ viết là 'assigns names' mà anh.

Họ nói không rõ. Các names trong Let có tầm vực (scope) trong vòng công thức. Đúng theo lối hiểu lập trình thì nó là variable names (identifiers), tức là biến
Tương tự như cách dùng biến trong lập trình, biến của Let có hai công dụng:
1. thâu gọn biểu thức tính trị của biến khi đưa vào làm tham số công thức chính - phần calculation cuối cùng.
2. giúp Excel giảm việc phải tính một biểu thức nhiều lần (khá quan trọng).

Tôi thấy từ Name thì tôi hiểu ở tầm vực rộng hơn, Sheet, Workbook,... Cho nên tôi coi nó là nhiệm vụ của Lambda.

Chú thích về tầm vực của biến: bạn bết Google Script thì chắc cũng rõ về JavaSrcip. Tầm vực của biến được mô tả rõ trong định nghĩa "closure"