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

[tranhungdao12a3]

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:

Em cảm ơn!

 

[befaint]

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

 

[Phuocam]

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:

Có cho dùng hàm tự tạo không? Hahaha ...

.

 

[Tình nghĩa giang hồ]

Có cho dùng hàm tự tạo không? Hahaha ...

.

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.

 

[hocexcel_1991]

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]"),"")

 

[Ronaldinho7]

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:
View attachment 279062
Em cảm ơn!

Tham khảo cách không dùn công thức.
Copy số tài khoản ở A5, A6 sang C5, C6
trỏ chuột ở C7-->ấn Ctrl+E (Flash Fill).

 

[Hoàng Tuấn 868]

Tham khảo cách không dùn công thức.
Copy số tài khoản ở A5, A6 sang C5, C6
trỏ chuột ở C7-->ấn Ctrl+E (Flash Fill).
View attachment 279070

Dòng 29 chuỗi có 11 số.

 

[Tình nghĩa giang hồ]

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

 

[Phuocam]

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

.

 

[Tình nghĩa giang hồ]

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 ?

 

[tranhungdao12a3]

Có cho dùng hàm tự tạo không? Hahaha ...

.

Được bạn, dùng công thức hay VBA, Power query,... gì cũng được bạn nhé!

 

[Phuocam]

Được bạn, dùng công thức hay VBA, Power query,... gì cũng được bạn nhé!

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)

.

 

[VetMini]

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.

 

[Phan Thế Hiệp]

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:
View attachment 279062
Em cảm ơn!

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

1. Có hay không số TK có số 0 đầu như 000123456789, hay 010100089000?
2. Chuỗi số do sự cố nhập liệu có hơn 12 số, như 1905170192600?
3. 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

 

[tranhungdao12a3]

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.

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

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

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

1. Có hay không số TK có số 0 đầu như 000123456789, hay 010100089000?
2. Chuỗi số do sự cố nhập liệu có hơn 12 số, như 1905170192600?
3. 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

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!

 

[VetMini]

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.

 

[VetMini]

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])"
...

 

[tranhungdao12a3]

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.

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