Công thức loại bỏ ký tự chữ trong 1 chuỗi !

[wtvnphu]

Nhờ mọi người viết dùm công thức để loại bỏ tất cả ký tự chữ trong 1 chuỗi văn bản có số và chữ với ạ.

 

[Phuocam]

Nhờ mọi người viết dùm công thức để loại bỏ tất cả ký tự chữ trong 1 chuỗi văn bản có số và chữ với ạ.

Yêu cầu này, có lẽ phải dùng UDF.

Bạn có muốn dùng nó?

.

 

[VetMini]

Yêu cầu này, có lẽ phải dùng UDF.

Bạn có muốn dùng nó?

.

Ở đây có mấy tay lọc giỏi lắm. Cái này dùng công thức khủng cũng được. Bạn bảo thớt chịu khó tìm (nhìn cái câu tâm niệm thì đáng lẽ phải là tay chịu khó).

 

[batman1]

Ở đây có mấy tay lọc giỏi lắm. Cái này dùng công thức khủng cũng được. Bạn bảo thớt chịu khó tìm (nhìn cái câu tâm niệm thì đáng lẽ phải là tay chịu khó).

Khủng thì đã sao? Hơi bất tiện nhưng dùng được là mừng rồi. Bác còn nhớ có người nhờ GPE rút gọn công thức dài hơn trang A4 không?

 

[VetMini]

Khủng thì đã sao? Hơi bất tiện nhưng dùng được là mừng rồi. Bác còn nhớ có người nhờ GPE rút gọn công thức dài hơn trang A4 không?

Công thức khủng hơi khó hiểu cho nên:
1. có thể lúc tìm, nó ngay trước mắt mà không biết
2. code và công thức ở đây viết theo kiểu đặc thù cho thân chủ. Người hỏi bài ở đây thì chỉ muốn a đến z. Có lụm được đúng cái công thức chưa chắc đã chịu tự chỉnh sửa (chỗ tham chiếu) cho hợp với mình.

 

[wtvnphu]

Công thức khủng hơi khó hiểu cho nên:
1. có thể lúc tìm, nó ngay trước mắt mà không biết
2. code và công thức ở đây viết theo kiểu đặc thù cho thân chủ. Người hỏi bài ở đây thì chỉ muốn a đến z. Có lụm được đúng cái công thức chưa chắc đã chịu tự chỉnh sửa (chỗ tham chiếu) cho hợp với mình.

Chào a VetMini,

Cái vụ lọc lấy dữ liệu trong 1 cell tách chữ ra chữ, số ra số là công việc thường gặp của em.

Cứ đụng chạm hoài mà vẫn chưa tìm được giải pháp tối ưu và ngắn gọn.

Các hàm cắt tách thay thế loại bỏ như mid, SUBSTITUTE ... thậm chí là các sub viết bằng vba của anh chị trên diễn đàn cũng không thỏa mãn được công việc.

Bài này đăng lên nhờ các anh chị có giải pháp nào có thể nhanh hơn và gọn hơn không, ví dụ tìm được giải thuật ký tự đại diện chẳng hạn để ghép vào hàm SUBSTITUTE ( nếu tìm được thì đỡ nhọc quá ).

Cảm ơn anh đã quan tâm bài ạ !

 

[batman1]

Trao đổi mà phải đợi hơn 2 tuần mới nhận được phản hồi thì nhiều người ngại. Mà người muốn giúp người ta hỏi thì lại lờ người ta đi, không thèm trả lời.

 

[wtvnphu]

Trao đổi mà phải đợi hơn 2 tuần mới nhận được phản hồi thì nhiều người ngại. Mà người muốn giúp người ta hỏi thì lại lờ người ta đi, không thèm trả lời.

Cảm ơn anh đã nhắc nhở, em không phải phất lờ, mà tìm hiểu thuật ngữ trên mà không tìm được.

Bài đã được tự động gộp: 15/3/22

Yêu cầu này, có lẽ phải dùng UDF.

Bạn có muốn dùng nó?

.

Anh Phuocam,

Cho em hỏi UDF là gì được không ? Em hơi gà mong anh thông cảm

 

[Phuocam]

Cảm ơn anh đã nhắc nhở, em không phải phất lờ, mà tìm hiểu thuật ngữ trên mà không tìm được.

Bài đã được tự động gộp: 15/3/22

Anh Phuocam,

Cho em hỏi UDF là gì được không ? Em hơi gà mong anh thông cảm

User-Defined Function viết tắt là UDF là hàm do người dùng định nghĩa.

Có những yêu cầu mà hàm có sẵn của Excel khó có thể đáp ứng được. Ví dụ trường hợp của bạn, dùng hơn 26 hàm SUBSTITUTE để thay thế a-z và các ký tự không mong muốn thành ký tự rỗng, người dùng có thể viết hàm riêng và sử dụng như:

=loaikytu(B2) hoặc =loaikytu("abcd1256n~gay895")

Kết quả là : 1256~895

Xem thêm: https://filegi.com/tech-term/user-defined-function-udf-2134/

 

[wtvnphu]

User-Defined Function viết tắt là UDF là hàm do người dùng định nghĩa.

Có những yêu cầu mà hàm có sẵn của Excel khó có thể đáp ứng được. Ví dụ trường hợp của bạn, dùng hơn 26 hàm SUBSTITUTE để thay thế a-z và các ký tự không mong muốn thành ký tự rỗng, người dùng có thể viết hàm riêng và sử dụng như:

=loaikytu(B2) hoặc =loaikytu("abcd1256n~gay895")

Kết quả là : 1256~895

Xem thêm: https://filegi.com/tech-term/user-defined-function-udf-2134/

Chào anh Phuocam,

Trường hợp dùng hàm tự định nghĩa thì khi cần xử lý dữ liệu ở file excel khác, người dùng phải nạp vào file khác như các code vba hả anh ?

Để viết hàm UDF thay cho 26 hàm SUBSTITUTE thì nó như thế nào? anh có thể viết dùm 1 hàm không ạ?

 

[hocexcel_1991]

Bạn thử dùng công thức này (áp dụng office>=2019)

=CONCAT(IF(MMULT(--(MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)=CHAR(TRANSPOSE(ROW($65:$90)))),ROW($65:$90)^0),"",MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)))

 

[Phuocam]

Chào anh Phuocam,

Trường hợp dùng hàm tự định nghĩa thì khi cần xử lý dữ liệu ở file excel khác, người dùng phải nạp vào file khác như các code vba hả anh ?

Để viết hàm UDF thay cho 26 hàm SUBSTITUTE thì nó như thế nào? anh có thể viết dùm 1 hàm không ạ?

Có 2 cách:

1. Hàm để trong file, phải lưu với phần mở rộng là ".xlsm" hoặc "xlsb". File này đem đi máy nào cũng sử dụng đươc.

2. Lưu riêng dưới dạng addin, bất cứ file nào trong máy này cũng sử dụng được. Đem file đi máy khác, hàm sẽ báo lỗi #NAME?

.

 

[VetMini]

Có 2 cách:

1. Hàm để trong file, phải lưu với phần mở rộng là ".xlsm" hoặc "xlsb". File này đem đi máy nào cũng sử dụng đươc.

2. Lưu riêng dưới dạng addin, bất cứ file nào trong máy này cũng sử dụng được. Đem file đi máy khác, hàm sẽ báo lỗi #NAME?

.

Còn cách thứ 3 nữa bạn ạ. Chỉ tại diễn đàn này khoái Add-ins cho nên tảng lờ nó đi.
3. Cho vào file PERSONAL.xlsm
Cũng như cách 1, phải tải file này vào các máy sử dụng nó. Tuy nhiên, nó giản dị hơn Add-ins nhiều.

Code:
Function LoaiKyTuAlpha(chuoi As String)
Const LOAI = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
chuoiLoai = LOAI & LCase(LOAI)
LoaiKyTuAlpha = chuoi
For i = 1 To Len(chuoiLoai)
LoaiKyTuAlpha = Replace(LoaiKyTuAlpha, Mid(chuoiLoai, i, 1), "")
Next i
End Function

Chú thích: tôi lười viết code loại này lắm. Đáng lẽ như thớt nói:


Công việc thường ngày, đụng chạm hoài mà không chịu học Power Query thì chắc chắn là "Không có gì là có thể với bạn" rồi.

Hai cái này chúng có vẻ không hạp nhau:

 

[Phuocam]

Còn cách thứ 3 nữa bạn ạ. Chỉ tại diễn đàn này khoái Add-ins cho nên tảng lờ nó đi.
3. Cho vào file PERSONAL.xlsm
Cũng như cách 1, phải tải file này vào các máy sử dụng nó. Tuy nhiên, nó giản dị hơn Add-ins nhiều.

Code:
Function LoaiKyTuAlpha(chuoi As String)
Const LOAI = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
chuoiLoai = LOAI & LCase(LOAI)
LoaiKyTuAlpha = chuoi
For i = 1 To Len(chuoiLoai)
LoaiKyTuAlpha = Replace(LoaiKyTuAlpha, Mid(chuoiLoai, i, 1), "")
Next i
End Function

Chú thích: tôi lười viết code loại này lắm. Đáng lẽ như thớt nói:

View attachment 273181
Công việc thường ngày, đụng chạm hoài mà không chịu học Power Query thì chắc chắn là "Không có gì là có thể với bạn" rồi.

Hai cái này chúng có vẻ không hạp nhau:

View attachment 273182
View attachment 273183

Code này luôn chạy 52 lần. Khà khà khà!

Một cách viết khác là chạy theo độ dài của chuỗi.

.

 

[VetMini]

Thêm: tôi quên nữa, cái hàm UDF này làm đẹp nhất là dùng RegEx. Và tôi tin là GPE có cả đống bài về cái này.
Thớt bao rằng mình tìm không có cái nào hợp. Tôi đặt câu hỏi: thớt có hiểu cái hàm ấy để nói rằng nó "hợp hay không hợp"? Hay là thớt đòi hỏi cái chỗ "tối ưu và ngắn gọn [sic]"?

Code này luôn chạy 52 lần. Khà khà khà!

Một cách viết khác là chạy theo độ dài của chuỗi.

.

Với điều kiện là chuỗi không dài lắm.

Chạy theo chuỗi nè:
Function LoaiKyTuAlpha(chuoi As String)
LoaiKyTuAlpha = Space(Len(chuoi))
For i = 1 To Len(chuoi)
Select Case Asc(Mid(chuoi, i, 1))
Case 65 To 90, 97 To 122
Case Else
i2 = i2 + 1
Mid(LoaiKyTuAlpha, i2, 1) = Mid(chuoi, i, 1)
End Select
Next i
LoaiKyTuAlpha = Left(LoaiKyTuAlpha, i2)
End Function

Code này dò thì nhiều nhưng xử lý chuỗi rất ít. Nó dùng hàm Mid để chỉnh ký tự tại chỗ cho nên không phải lập chuõi mới và bỏ chuỗi cũ như hàm replace.
Ưu điểm là nó không sợ chuỗi dài. Nhược điểm là nó hơi khó sửa, thêm bớt các ký tự cần loại.

 

[Phuocam]

Thêm: tôi quên nữa, cái hàm UDF này làm đẹp nhất là dùng RegEx. Và tôi tin là GPE có cả đống bài về cái này.
Thớt bao rằng mình tìm không có cái nào hợp. Tôi đặt câu hỏi: thớt có hiểu cái hàm ấy để nói rằng nó "hợp hay không hợp"? Hay là thớt đòi hỏi cái chỗ "tối ưu và ngắn gọn [sic]"?


Với điều kiện là chuỗi không dài lắm.

Chạy theo chuỗi nè:
Function LoaiKyTuAlpha(chuoi As String)
LoaiKyTuAlpha = Space(Len(chuoi))
For i = 1 To Len(chuoi)
Select Case Asc(Mid(chuoi, i, 1))
Case 65 To 90, 97 To 122
Case Else
i2 = i2 + 1
Mid(LoaiKyTuAlpha, i2, 1) = Mid(chuoi, i, 1)
End Select
Next i
LoaiKyTuAlpha = Left(LoaiKyTuAlpha, i2)
End Function

Code này dò thì nhiều nhưng xử lý chuỗi rất ít. Nó dùng hàm Mid để chỉnh ký tự tại chỗ cho nên không phải lập chuõi mới và bỏ chuỗi cũ như hàm replace.
Ưu điểm là nó không sợ chuỗi dài. Nhược điểm là nó hơi khó sửa, thêm bớt các ký tự cần loại.

Cách này dùng toán tử "Like".

Function LoaiKyTuAlpha(ByVal chuoi As String) As String
Dim i As Long
For i = 1 To Len(chuoi)
    If Mid(chuoi, i, 1) Like "[a-zA-Z]" Then Mid(chuoi, i, 1) = "a"
Next i
LoaiKyTuAlpha = Replace(chuoi, "a", "")
End Function

.

 

[VetMini]

Cách này dùng toán tử "Like".

Function LoaiKyTuAlpha(ByVal chuoi As String) As String
Dim i As Long
For i = 1 To Len(chuoi)
    If Mid(chuoi, i, 1) Like "[a-zA-Z]" Then Mid(chuoi, i, 1) = "a" ' dùng "b" thì giảm đi được 1 ký tự để dò
Next i
LoaiKyTuAlpha = Replace(chuoi, "a", "")
End Function

.

Toi có nói là cách "theo độ dài chuỗi" hiệu quả hơn nếu chuỗi ngắn.
Like là một hình thức của RegEx. Chỉ là RegEx engine nó cấp thêm một số hàm mạnh hơn thôi. Tôi tin là cỗ máy RegEx rất tốt. Có thể bên trong nó sửa [A-Z] thành >='A' và <='Z' (code C/C++ coi ký tự là một số, và 'A' tương đương với 65). Like của VBA chưa chắc đã làm vậy.

Lưu ý là cái code "chạy 52 lần" ở bài #13 tuy sử dụng Replace hơi nhiều nhưng ngược lại mỗi lần Replace thì chuỗi ngắn đi. Nếu chuỗi dài và số ký tự alpha đáng kể thì nó cũng khá tốt.

 

[wtvnphu]

Còn cách thứ 3 nữa bạn ạ. Chỉ tại diễn đàn này khoái Add-ins cho nên tảng lờ nó đi.
3. Cho vào file PERSONAL.xlsm
Cũng như cách 1, phải tải file này vào các máy sử dụng nó. Tuy nhiên, nó giản dị hơn Add-ins nhiều.

Code:
Function LoaiKyTuAlpha(chuoi As String)
Const LOAI = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
chuoiLoai = LOAI & LCase(LOAI)
LoaiKyTuAlpha = chuoi
For i = 1 To Len(chuoiLoai)
LoaiKyTuAlpha = Replace(LoaiKyTuAlpha, Mid(chuoiLoai, i, 1), "")
Next i
End Function

Chú thích: tôi lười viết code loại này lắm. Đáng lẽ như thớt nói:

View attachment 273181
Công việc thường ngày, đụng chạm hoài mà không chịu học Power Query thì chắc chắn là "Không có gì là có thể với bạn" rồi.

Hai cái này chúng có vẻ không hạp nhau:

View attachment 273182
View attachment 273183

Chào anh VetMini và anh Phuocam,

Không biết em có áp dụng sai ý các anh hay không ?

Em cho vào file .xlsm mà sử dụng không được.

2 anh chỉ dẫn thêm nhé,

Cảm ơn 2 anh.

 

[batman1]

Chào anh VetMini và anh Phuocam,

Không biết em có áp dụng sai ý các anh hay không ?

Em cho vào file .xlsm mà sử dụng không được.

2 anh chỉ dẫn thêm nhé,

Cảm ơn 2 anh.

Khi hỏi thì sao không mô tả kỹ, nói to nói rõ nói huỵch toẹt ra? Người ta làm cho trường hợp chỉ có các ký tự a-zA-Z chứ không cho cả ký tự tiếng Việt.

Mà chép code vd. như thế này thì bó tay rồi.

Function LoaiKyTuAlpha1(chuoi As String)
Const LOAI = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
chuoiLoai = LOAI & LCase(LOAI)
LoaiKyTuAlpha = chuoi
For i = 1 To Len(chuoiLoai)
LoaiKyTuAlpha = Replace(LoaiKyTuAlpha, Mid(chuoiLoai, i, 1), "")
Next i
End Function

Hàm tên là LoaiKyTuAlpha1, nhưng trong thân hàm thì lại thao tác với LoaiKyTuAlpha nên hàm LoaiKyTuAlpha1 trả về 0 là đúng rồi.

 

[wtvnphu]

Khi hỏi thì sao không mô tả kỹ, nói to nói rõ nói huỵch toẹt ra? Người ta làm cho trường hợp chỉ có các ký tự a-zA-Z chứ không cho cả ký tự tiếng Việt.

Anh batman1,

Em test thử không dấu và có dấu đều không được.

Em đang nghĩ là em áp dụng sai cách.