Nhờ giúp công thức tách chuỗi số dài 12 số trong chuỗi

Chia sẻ bởi:hands

★★★★★

‹ Hàm sắp xếp mảng ngang dọc với... Hàm UDF tính từng dòng trong một... ›

Quảng cáo

Chào các anh chị!
Em có vấn đề nhờ anh chị giúp công thức:
Em cần tách chuỗi số dài 12 số trong chuỗi cột A sang cột B.
Trong cột A có nhiều chuỗi số nhưng chỉ tách chuỗi có 12 số ra thôi. Dòng nào không có thì bỏ qua:
279062
Em cảm ơn!

=IFERROR( ""& AGGREGATE(14,6, --MID(A2&"|", ROW(INDIRECT("1:"&LEN(A2&"|"))),12),1),"")

www.giaiphapexcel.com/diendan/threads/nh%E1%BB%9D-gi%C3%BAp-c%C3%B4ng-th%E1%BB%A9c-t%C3%A1ch-chu%E1%BB%97i-s%E1%BB%91-d%C3%A0i-12-s%E1%BB%91-trong-chu%E1%BB%97i.161293/post-1077467

VBA cũng được anh, anh Befaint đã giải bằng công thức rồi. Nhưng đó chỉ là 1 cách giải, còn nếu anh làm bằng VBA hay cách khác thì giải cho mọi người cùng xem. Có nhiều cách giải để có anh em tham khảo với, chứ chỉ giới hạn một cách giải…….thì mọi người đâu có học hỏi được nhiều. Em cũng thích đọc bài viết nào mà có nhiều cách giải, nhiều đáp án.

Thử dùng công thức này, trong trường hợp chuỗi lớn hơn 12 số thì bỏ qua.

=IFERROR(FILTERXML("<m><n>"&SUBSTITUTE(TRIM(CONCAT(TEXT(MID(A2,SEQUENCE(,LEN(A2)),1),"0;;0; ")))," ","</n><n>")&"</n></m>","//n[string-length()=12]"),"")

www.giaiphapexcel.com/diendan/threads/nh%E1%BB%9D-gi%C3%BAp-c%C3%B4ng-th%E1%BB%A9c-t%C3%A1ch-chu%E1%BB%97i-s%E1%BB%91-d%C3%A0i-12-s%E1%BB%91-trong-chu%E1%BB%97i.161293/post-1077476

Xin góp vui bằng VBA

Public Function tach_chuoi_12_ky_tu(ByVal Txt As Range) As String
Dim Tmp, J As Long
Tmp = Split(Txt, " ")
For J = 0 To UBound(Tmp)
If Len(Tmp(J)) = 12 Then
tach_chuoi_12_ky_tu = Trim(tach_chuoi_12_ky_tu & " " & Tmp(J))
End If
Next J
End Function

If Len(Tmp(J)) = 12 Then
Nó không phân biệt số và chuỗi, nên là:

If tmp(j) like "############" then

If tmp(j) like "############" then
Cái này là lấy số có 12 ký tự thì viết vậy.
Còn trường hợp chỉ lấy chữ có 12 ký tự thì viết sao anh ?

Bạn dùng thử hàm tự tạo sau:

Public Function TachSo(ByVal s As String, ByVal n As Long) As String
'n: chieu dai cua so
Dim i As Long, v As Variant, tmp As String
For i = 1 To Len(s)
If Mid(s, i, 1) Like "#" = False Then Mid(s, i, 1) = " "
Next i
For Each v In Split(Application.Trim(s), " ")
If Len(v) = n Then tmp = tmp & ", " & v
Next v
If Len(tmp) > 0 Then TachSo = VBA.Mid(tmp, 3)
End Function

Cách dùng:

=TachSo(A2,12)

Ví dụ bạn muốn lấy chuỗi có 9 số thì dùng:

=TachSo(A2,9)

www.giaiphapexcel.com/diendan/threads/nh%E1%BB%9D-gi%C3%BAp-c%C3%B4ng-th%E1%BB%A9c-t%C3%A1ch-chu%E1%BB%97i-s%E1%BB%91-d%C3%A0i-12-s%E1%BB%91-trong-chu%E1%BB%97i.161293/post-1077500

.
Nhiều hơn 1 thì sao? Chỉ lấy 1 đầu tiên?
Cái này nếu VBA thì dùng Regex là đúng chỗ dùng nhất.

Bạn cần xác định thêm các trường hợp có thể có xảy ra, ví như:

Có hay không số TK có số 0 đầu như 000123456789, hay 010100089000?

Chuỗi số do sự cố nhập liệu có hơn 12 số, như 1905170192600?

Có hay không dữ liệu có 2 hoặc nhiều chuỗi số cùng có 12 số?

Bạn càng làm rõ, thì lập công thức càng chính xác hơn.
Thân

Không có xuất hiện 2 lần đâu anh, chỉ 1 lần duy nhất thôi ạ

1. TK có thể có số 0 bắt đầu
2. Có khả năng nhập liệu vượt 12 số, nhưng số TK nó là một nhóm ký tự rời, được phân tách với chuỗi ký tự khác bởi dấu cách hoặc dấu /,-, cho nên chỉ lấy nhóm 12 số, nếu mã nào >12 số mà cho biết nhập sai thì càng tốt bạn ạ.
3. Không có trường hợp 2 nhóm 12 số trong chuỗi, do mỗi mã TK là định danh riêng nên một ô sẽ không có 2 chuỗi 12 số trong ô đó.
Cảm ơn bạn!

Function LayChuoiSo(s As String, n As Long) As String
' returns the first numeric string of exactly n digits
' if such exists in string s
' otherwise, returns blank
Dim matches
With CreateObject("VBScript.RegExp")
.Pattern = "(^|[^d])([d]{" & n & "})($|[^d])"
.Global = True
If .test(s) Then
Set matches = .Execute(s)
LayChuoiSo = matches(0).submatches(1)
End If
End With
End Function

Nếu máy cũ rích thì nên chỉnh cái Regex thành biến Static. Giúp cho máy khỏi phải dựng ojbect mỗi lượt hàm chạy (1000 dòng thì nó lên 1000 lần)
Nhưng nếu máy không cũ lắm thì hệ thống Windows hỗ trợ Script rất tốt. Regex sẽ được cached rất hiệu quả cho nên không cần code giữ lại.
Nếu muốn mở rộng hơn thì sửa khúc đầu như sau:

Function LayChuoiSo(s As String, Optionnal n As Long = 0) As String
' returns the first numeric string found inside string s
' n>0 : string of exactly n digits; n=0 or omitted: the first numeric string, any length
' if s does not contain any numeric as specified, returns blank
Dim matches
With CreateObject("VBScript.RegExp")
.Pattern = "(^|[^d])([d]" & IIF(n, "{" & n & "}", "+"))($|[^d])"
…

Cảm ơn anh đã giúp đỡ, kết quả chạy tốt rồi anh!

www.giaiphapexcel.com/diendan/threads/nh%E1%BB%9D-gi%C3%BAp-c%C3%B4ng-th%E1%BB%A9c-t%C3%A1ch-chu%E1%BB%97i-s%E1%BB%91-d%C3%A0i-12-s%E1%BB%91-trong-chu%E1%BB%97i.161293/post-1077640