Hàm tách số trong chuỗi ra nhiều cột

Liên hệ QC
hongphuong1997

hongphuong1997

Thành viên tiêu biểu
Tham gia
12/11/17
Bài viết
718
Được thích
288
Giới tính
Nữ
Nhờ anh chị và thầy cô viết giúp hàm như file đính kèm ạ
Em xin cảm ơn
 

File đính kèm

  • Tách số trong chuỗi ra nhiều cột.xlsb
    8.5 KB · Đọc: 24
Sắp xếp theo thời gian sắp xếp theo bầu chọn
HieuCD đã viết:
Không được cũng làm, ai dám cấm :p
Nhấp chuột vào đây để mở rộng...
Lúc trước cháu có trả lời anh
huonglien1901
roài, mà bài bị lạc đâu mất bây giờ không nhìn thấy bác oi
Bài đã được tự động gộp:

snow25 đã viết:
Thử nhé em.
Nhấp chuột vào đây để mở rộng...
Hi hi... em cảm ơn anh @snow25 rất nhiều, chuẩn quá rùi anh oi
Chúc anh và toàn thể GPE vui vẻ và có nhiều thành đạt anh nhé!
 
Upvote 0
Loại bài này có từng giải ít ra là đôi lần.
Cách gọn nhất là dùng Regex.

Mã: 
Function LocSo(chuoi As String) As String() ' As Variant cũng được
' lấy tất cả các số trong mọt chuỗi, trả về môt mảng những số này
Static RX As Object
If RX Is Nothing Then
  Set RX = CreateObject("vbscript.regexp")
  RX.Global = True
End If
RX.Pattern = "[^\d]+" ' khi hàm chạy êm rồi thì đem dòng này cho vào vùng IF ở trên
LocSo = Split(Application.Trim(RX.Replace(chuoi, " ")), " ")
End Function

Hàm sẽ trả về một mảng các số. Để biết cách dùng thi tham khảo các bài tôi viết về loại hàm trả về mảng.

Cách dùng cho bài này (sử dụng mảng):
- quét chọn 3 ô B13:C13
- gõ công thức =LocSo($B2)
- Ctrl+Shift_Enter (3 ô được chọn sẽ thành 1 mảng)
- Fill xuống các dòng còn lại
Lưu ý là vì tánh chất mảng của Excel, nếu hàm chỉ trả về 1 số thì cả 3 ô đều lấy trị số này. Nếu hàm trả về 2 số thì ô thứ 3 sẽ được #Value!

Cách dùng theo dạng từng ô (lưu ý là cách này buộc con toán thực hiện lại cho mỗi ô, vì vậy không hiệu quả bằng cách trên)
- Ô B13 gõ công thức =Index(LocSo($B2), 1); ô C13 =Index(LocSo($B2), 2)); ô C13 =Index(LocSo($B2), 3)
(thay 1 bằng Column()-1 và kéo qua 2 ô còn lại cũng được)
- Fill xuống
 
Upvote 0
vanaccex đã viết:
Bạn thử dùng cách này xem !
Nhấp chuột vào đây để mở rộng...
Ai đã hướng dẫn bạn cách sử dụng Regexp vậy, bạn có cần được hướng dẫn thêm không.
Học thêm cặp dấu ngoặc tròn () này này, nó là một Group
(?:\d+) có nghĩa là lấy các số nhưng nó không phải một group
(?=\d+) có nghĩa là lấy ký tự đằng trước nếu kết hợp với số
(?!\d+) nó ngược với ?=

.Pattern = "(\d+)"

Ở trường hợp này nên viết như thế này sẽ tốt hơn:

.Pattern = "[\D]+(\d{9,})[\D]+(\d{9,})[\D]+(\d+)[\D]*"

Mặc dù dài hơn nhưng phân tích chính xác hơn

PHP: 
Sub PhanTich_1()
Dim SArr, Result, aDuLieu, i, t, j
SArr = Sheet1.Range("A1", Sheet1.Range("A1").End(xlDown))
ReDim Result(1 To UBound(SArr), 1 To 3)
With CreateObject("VbScript.RegExp")
    .Pattern = "(\d+)"
    .Global = True
    For i = 1 To UBound(SArr)
        If .test(SArr(i, 1)) Then
            Set t = .Execute(SArr(i, 1))
            For j = 1 To t.Count
                Result(i, j) = t(j - 1)
            Next
        End If
    Next i
    Sheet1.Range("B2").Resize(UBound(SArr), 3).NumberFormat = "@"
    Sheet1.Range("B2").Resize(UBound(SArr), 3) = Result
End With
End Sub
Sub PhanTich_2()
Dim SArr, Result, aDuLieu, i, t, j
SArr = Sheet1.Range("A1", Sheet1.Range("A1").End(xlDown))
ReDim Result(1 To UBound(SArr), 1 To 3)
With CreateObject("VbScript.RegExp")
    .Pattern = "[\D]+(\d{9,})[\D]+(\d{9,})[\D]+(\d+)[\D]*"
    .Global = False
    For i = 1 To UBound(SArr)
        If .test(SArr(i, 1)) Then
            Set t = .Execute(SArr(i, 1))
            Result(i, 1) = t(0).submatches(0)
            Result(i, 2) = t(0).submatches(1)
            Result(i, 3) = t(0).submatches(2)
        End If
    Next i
    Sheet1.Range("B2").Resize(UBound(SArr), 3).NumberFormat = "@"
    Sheet1.Range("B2").Resize(UBound(SArr), 3) = Result
End With
End Sub
 
Lần chỉnh sửa cuối:
Upvote 0
HeSanbi đã viết:
Ai đã hướng dẫn bạn cách sử dụng Regexp vậy, bạn có cần được hướng dẫn thêm không.
Học thêm cặp dấu ngoặc tròn () này này, nó là một Group
(?:\d+) có nghĩa là lấy các số nhưng nó không phải một group
(?=\d+) có nghĩa là lấy ký tự đằng trước nếu kết hợp với số
(?!\d+) nó ngược với ?=

.Pattern = "(\d+)"

Ở trường hợp này nên viết như thế này sẽ tốt hơn:

.Pattern = "[\D]*(\d{9,})[\D]*(\d{9,})[\D]*(\d+)[\D]*"

Mặc dù dài hơn nhưng phân tích chính xác hơn

PHP: 
Sub PhanTich_1()
Dim SArr, Result, aDuLieu, i, t, j
SArr = Sheet1.Range("A1", Sheet1.Range("A1").End(xlDown))
ReDim Result(1 To UBound(SArr), 1 To 3)
With CreateObject("VbScript.RegExp")
    .Pattern = "(\d+)"
    .Global = True
    For i = 1 To UBound(SArr)
        If .test(SArr(i, 1)) Then
            Set t = .Execute(SArr(i, 1))
            For j = 1 To t.Count
                Result(i, j) = t(j - 1)
            Next
        End If
    Next i
    Sheet1.Range("B2").Resize(UBound(SArr), 3).NumberFormat = "@"
    Sheet1.Range("B2").Resize(UBound(SArr), 3) = Result
End With
End Sub
Sub PhanTich_2()
Dim SArr, Result, aDuLieu, i, t, j
SArr = Sheet1.Range("A1", Sheet1.Range("A1").End(xlDown))
ReDim Result(1 To UBound(SArr), 1 To 3)
With CreateObject("VbScript.RegExp")
    .Pattern = "[\D]*(\d{9,})[\D]*(\d{9,})[\D]*(\d+)[\D]*"
    .Global = False
    For i = 1 To UBound(SArr)
        If .test(SArr(i, 1)) Then
            Set t = .Execute(SArr(i, 1))
            Result(i, 1) = t(0).submatches(0)
            Result(i, 2) = t(0).submatches(1)
            Result(i, 3) = t(0).submatches(2)
        End If
    Next i
    Sheet1.Range("B2").Resize(UBound(SArr), 3).NumberFormat = "@"
    Sheet1.Range("B2").Resize(UBound(SArr), 3) = Result
End With
End Sub
Nhấp chuột vào đây để mở rộng...
Dạ vâng ! em Vân cảm ơn anh @HeSanbi nhiều ạ !

Anh có tài liệu nào về regex anh có thể share cho em Vân với ạ !

Em Vân sẽ tìm hiểu thêm về vấn đề này ạ !
 
Upvote 0
HeSanbi đã viết:
Ai đã hướng dẫn bạn cách sử dụng Regexp vậy, bạn có cần được hướng dẫn thêm không.
Học thêm cặp dấu ngoặc tròn () này này, nó là một Group
(?:\d+) có nghĩa là lấy các số nhưng nó không phải một group
(?=\d+) có nghĩa là lấy ký tự đằng trước nếu kết hợp với số
(?!\d+) nó ngược với ?=

.Pattern = "(\d+)"

Ở trường hợp này nên viết như thế này sẽ tốt hơn:

.Pattern = "[\D]+(\d{9,})[\D]+(\d{9,})[\D]+(\d+)[\D]*"

Mặc dù dài hơn nhưng phân tích chính xác hơn

PHP: 
Sub PhanTich_1()
Dim SArr, Result, aDuLieu, i, t, j
SArr = Sheet1.Range("A1", Sheet1.Range("A1").End(xlDown))
ReDim Result(1 To UBound(SArr), 1 To 3)
With CreateObject("VbScript.RegExp")
    .Pattern = "(\d+)"
    .Global = True
    For i = 1 To UBound(SArr)
        If .test(SArr(i, 1)) Then
            Set t = .Execute(SArr(i, 1))
            For j = 1 To t.Count
                Result(i, j) = t(j - 1)
            Next
        End If
    Next i
    Sheet1.Range("B2").Resize(UBound(SArr), 3).NumberFormat = "@"
    Sheet1.Range("B2").Resize(UBound(SArr), 3) = Result
End With
End Sub
Sub PhanTich_2()
Dim SArr, Result, aDuLieu, i, t, j
SArr = Sheet1.Range("A1", Sheet1.Range("A1").End(xlDown))
ReDim Result(1 To UBound(SArr), 1 To 3)
With CreateObject("VbScript.RegExp")
    .Pattern = "[\D]+(\d{9,})[\D]+(\d{9,})[\D]+(\d+)[\D]*"
    .Global = False
    For i = 1 To UBound(SArr)
        If .test(SArr(i, 1)) Then
            Set t = .Execute(SArr(i, 1))
            Result(i, 1) = t(0).submatches(0)
            Result(i, 2) = t(0).submatches(1)
            Result(i, 3) = t(0).submatches(2)
        End If
    Next i
    Sheet1.Range("B2").Resize(UBound(SArr), 3).NumberFormat = "@"
    Sheet1.Range("B2").Resize(UBound(SArr), 3) = Result
End With
End Sub
Nhấp chuột vào đây để mở rộng...
Em cảm ơn anh
HeSanbi
Chúc anh mùa giáng sinh vui vẻ anh nhé!
 
Upvote 0
vanaccex đã viết:
Bạn thử dùng cách này xem !
Nhấp chuột vào đây để mở rộng...
Đã bắt đúng dãy số liên tục thì xuất luôn kết quả. Bạn thay 3 dòng trên thành 3 dòng dưới xem sao
Mã: 
'Result(i, 1) = Split(t(0), " ")(0)
'Result(i, 2) = Split(t(1), " ")(0)
'Result(i, 3) = Split(t(2), " ")(0)
            
Result(i, 1) = t(0)
Result(i, 2) = t(1)
Result(i, 3) = t(2)
 
Upvote 0
CHAOQUAY đã viết:
Đã bắt đúng dãy số liên tục thì xuất luôn kết quả. Bạn thay 3 dòng trên thành 3 dòng dưới xem sao
Mã: 
'Result(i, 1) = Split(t(0), " ")(0)
'Result(i, 2) = Split(t(1), " ")(0)
'Result(i, 3) = Split(t(2), " ")(0)
           
Result(i, 1) = t(0)
Result(i, 2) = t(1)
Result(i, 3) = t(2)
Nhấp chuột vào đây để mở rộng...
Dạ vâng em Vân cảm ơn anh @CHAOQUAY ạ
 
Upvote 0
Có sửa lại bài tí tẹo.
PHP: 
=IF($B2="","",TRIM(MID(SUBSTITUTE(MID($B2,LOOKUP(LEN($B2),FIND({0;1;2;3;4;5;6;7;8;9},$B2)-1),LEN($B2))," ",REPT(" ",100)),1+(2*(COLUMN(A1)-1))*100,100)))
 

File đính kèm

  • Tách số trong chuỗi ra nhiều cột.xlsb
    15.6 KB · Đọc: 9
Upvote 0
Upvote 0
Bạn phải đăng nhập hoặc đăng ký để trả lời bài viết tại đây.
Web KT

Bài viết mới nhất

test1986
  • Question
HELP! Sử dụng combobox để cập nhật dữ liệu trong file Excel
Trả lời
17
Đọc
501
ngochienbmt
N
N
Hàm if, lọc dữ liệu trùng
Trả lời
6
Đọc
6K
OANHMAI123
O
NguyenthiH
  • Question
Code lấy dữ liệu cho sheet!TonDau.
Trả lời
5
Đọc
129
HUONGHCKT
H
T
Xóa dòng theo điều kiện
Trả lời
1
Đọc
47
SA_DQ
SA_DQ
dungluf123
Lỗi khi sử dụng công thức để link từ file này sang file khác của Excel trên hệ thống mạng chung của công ty
Trả lời
0
Đọc
27
dungluf123
dungluf123
Xem thêm…

Facebook

Giaiphapexcel.com

Group

DIỄN ĐÀN GIẢI PHÁP EXCEL

Thành viên có số lượng bài viết cao nhất tháng

Thành viên có điểm tương tác cao nhất tháng

Back
Top Bottom