Anh chị giúp e tìm số điện thoại trong excel

excel_lv1.5 · 8/1/18

Nãy rảnh task xíu mình viết lại code. Bạn nào thích thì viết lại thành Function cho bạn ấy.

Mã:

Option Explicit
Private Sub mainPROCEDUCE()
On Error Resume Next
    'Add "Microsoft Visual Basic for Applications Extensibility 5.3"
    Call AddVBE
 
    'Add "Microsoft VBScript Regular Expressions 5.5" library
    Call AddReference
    Call getPhoneNumber
End Sub
Private Sub AddVBE()
    Dim vbProj, chkRef
    Dim BoolExists As Boolean

    Set vbProj = ActiveWorkbook.VBProject
    '~~> Check if "Microsoft Visual Basic for Applications Extensibility 5.3" is already added
            For Each chkRef In vbProj.References
                    If chkRef.Name = "Microsoft Visual Basic for Applications Extensibility 5.3" Then
                        BoolExists = True
                        GoTo CleanUp
                    End If
                Next
            vbProj.References.AddFromGuid "{0002E157-0000-0000-C000-000000000046}", 0, 0
CleanUp:
    If BoolExists = True Then
        MsgBox "Reference already exists"
    Else
        MsgBox "Reference Added Successfully"
    End If

    Set vbProj = Nothing
End Sub
Private Sub AddReference()
    Dim VBAEditor As VBIDE.VBE
    Dim vbProj As VBIDE.VBProject
    Dim chkRef As VBIDE.Reference
    Dim BoolExists As Boolean

    Set VBAEditor = Application.VBE
    Set vbProj = ActiveWorkbook.VBProject
    '~~> Check if "Microsoft VBScript Regular Expressions 5.5" is already added
            For Each chkRef In vbProj.References
                    If chkRef.Name = "VBScript_RegExp_55" Then
                        BoolExists = True
                        GoTo CleanUp
                    End If
                Next
            vbProj.References.AddFromFile "C:\WINDOWS\system32\vbscript.dll\3"
CleanUp:
    If BoolExists = True Then
        MsgBox "Reference already exists"
    Else
        MsgBox "Reference Added Successfully"
    End If

    Set vbProj = Nothing
    Set VBAEditor = Nothing
End Sub
Private Sub getPhoneNumber()
    Dim regex As Object, RNG, firstRNG As Range, firstROW As Integer, lastROW As Long, strDATA As String
    Dim Match, matches As Object
    Set regex = CreateObject("VBScript.RegExp")
    Set firstRNG = ThisWorkbook.Worksheets("DATA").Range("firstRNG")
    firstROW = 1
    lastROW = firstRNG.CurrentRegion.Rows.Count
    For RNG = firstROW To lastROW
        strDATA = ThisWorkbook.Worksheets("DATA").Range("C" & RNG)
        Set matches = regex.Execute(strDATA)
 
        With regex
          .Pattern = "(?:(\s)\d\d\s*\-*\d\s*\-*\d\s*\-*\d\s*\-*\d\s*\-*\d\s*\-*\d\s*\-*\d\s*\-*\d\s*\-*\d)"
          .Global = True
        End With
 
        For Each Match In matches
          Debug.Print Match.Value '
        Next Match
    Next RNG
End Sub

[\code]

Số điện thoại có 10 số thì sao bạn?

moctriet · 9/1/18

excel_lv1.5 đã viết:
Số điện thoại có 10 số thì sao bạn?

Cái d cuối cùng cho nó thêm dấu *
Nói thêm cái này rất nhiều ngoại lệ. Nên cover hết thì viết khá nhiều xử lý đấy. Để tập viết regex cũng được nhưng hơi nhọc.

be_09 · 9/1/18

noithat89 đã viết:
em có 1 file như hình

Cột A | Cột B
Hiền Supi | C ơi e lấy áo phao dài đen . E 53kg. Cao 157. Sdt.0968111431
Hong Anh | E lấy 1 đen nhé. 0965523315
Răng Khểnh | A300 màu đen c nhé 0966730715
Cột A là tên ạ, Cột B là text + sđt
giờ e muốn lấy được số điện thoại ra ở cột C thì có cách nào không ạ
Cảm ơn mọi người đã giúp đỡ e

Nếu dữ liệu File bạn bắt đầu là A1, bạn sử dụng hàm thế này:

=TachSoDT($A1)

Nó sẽ tách đầu số điện thoại có từ 10 số trở lên.

excel_lv1.5 · 9/1/18

moctriet đã viết:
Cái d cuối cùng cho nó thêm dấu *
Nói thêm cái này rất nhiều ngoại lệ. Nên cover hết thì viết khá nhiều xử lý đấy. Để tập viết regex cũng được nhưng hơi nhọc.

Tôi thấy là không xử lý được với các trường hợp có dấu \. và \s. Còn đối với bài này tôi thấy chỉ cần cái pattern="\d{10,11}" là đủ.

moctriet · 9/1/18

excel_lv1.5 đã viết:
Tôi thấy là không xử lý được với các trường hợp có dấu \. và \s. Còn đối với bài này tôi thấy chỉ cần cái pattern="\d{10,11}" là đủ.

Mục tiêu của code phải phủ đc càng nhiều ngoại lệ càng tốt. Đúng bài này khá đơn giản vì dữ liệu ít ngoại lệ. Trên thực tế đôi khi phải dùng cả đâu số điện thoại và count ký tự, trim nữa.

moctriet · 9/1/18

Mình gợi ý cách đa lựa chọn hơn. Cách dưới mình dùng sakura để kiếm tra, lười viết trên regex của vba
Bước 1: Thay thế hết toàn bộ ký tự không phải số
(?!\r\n)\D

Bước 2: Lọc các số theo đầu số nhà mạng
(086|096|097|098|0162|0163|0164|0165|0166|0167|0168|0169|090|093|0120|0121|0122|0126|0128|091|094|0123|0124|0125|0127|0129|092|0188|0186)\d{7}

Với cách này có thể lọc ra theo từng nhà mạng mobi, vina, viettel, vietnam mobile. Cũng có thể viết được bằng công thức trên excel.

HieuCD · 9/1/18

be09 đã viết:
Nếu dữ liệu File bạn bắt đầu là A1, bạn sử dụng hàm thế này:

=TachSoDT($A1)

Nó sẽ tách đầu số điện thoại có từ 10 số trở lên.

View attachment 189639

Bài gốc có nhiều số điện thoại dạng:
01655 833 879. Chị ui e muốn lấy áo phao dáng dài màu đen ạ.
Gửi giúp e về địa chỉ 87 Nguyễn Trãi tphd.01683.603.783

be_09 · 9/1/18

moctriet đã viết:
Mình gợi ý cách đa lựa chọn hơn. Cách dưới mình dùng sakura để kiếm tra, lười viết trên regex của vba
Bước 1: Thay thế hết toàn bộ ký tự không phải số
(?!\r\n)\D

Bước 2: Lọc các số theo đầu số nhà mạng
(086|096|097|098|0162|0163|0164|0165|0166|0167|0168|0169|090|093|0120|0121|0122|0126|0128|091|094|0123|0124|0125|0127|0129|092|0188|0186)\d{7}

Với cách này có thể lọc ra theo từng nhà mạng mobi, vina, viettel, vietnam mobile. Cũng có thể viết được bằng công thức trên excel.

Cái này gọi là vẽ đường cho hưu chạy rồi mình chạy theo nè.
Người sử dụng thấy cái gì chưa hợp lý hoặc vướng mắc người ta sẽ la làng lên liền.

moctriet · 9/1/18

be09 đã viết:
Cái này gọi là vẽ đường cho hưu chạy rồi mình chạy theo nè.
Người sử dụng thấy cái gì chưa hợp lý hoặc vướng mắc người ta sẽ la làng lên liền.

Rút kinh nghiệm

be_09 · 9/1/18

HieuCD đã viết:
Bài gốc có nhiều số điện thoại dạng:
01655 833 879. Chị ui e muốn lấy áo phao dáng dài màu đen ạ.
Gửi giúp e về địa chỉ 87 Nguyễn Trãi tphd.01683.603.783

Chủ Topic thấy cái gì chưa đúng thì cần liệt kê hết những cái ngoại lệ ra (lỗi do nhập liệu).
Dữ liệu trong File nhiều quá nên anh xóa bớt mà không kiểm tra lại.

HieuCD · 9/1/18

be09 đã viết:
Cái này gọi là vẽ đường cho hưu chạy rồi mình chạy theo nè.
Người sử dụng thấy cái gì chưa hợp lý hoặc vướng mắc người ta sẽ la làng lên liền.

Mình muốn tham khảo cách viết regex trong trường hợp rắc rối nầy nên mới gợi ý
Chúc bạn 1 ngày vui

moctriet · 9/1/18

HieuCD đã viết:
Mình muốn tham khảo cách viết regex trong trường hợp rắc rối nầy nên mới gợi ý
Chúc bạn 1 ngày vui

Cái trên của mình gợi ý rồi đó bạn. Nó còn cover được cả trường hợp trước đó người nhập nhầm số nào đó vào như thêm số 1, A1, A10 gì đó.
Cái regex này mình dùng trước khi cả học vba nên mình thích nó lắm. Mỗi tội mỗi lần viết tốn nhiều chất xám quá

be_09 · 9/1/18

HieuCD đã viết:
Mình muốn tham khảo cách viết regex trong trường hợp rắc rối nầy nên mới gợi ý
Chúc bạn 1 ngày vui

Chắc phải chờ chủ Topic sử dụng thử, rồi liệt kê hết tất cả các tình huống mới biết được các tình huống đó thế nào mới đề ra cách xử lý?
Chủ Topic đưa ra 1 đống dữ liệu mình không thể kiểm tra hết được.

excel_lv1.5 · 9/1/18

HieuCD đã viết:
Bài gốc có nhiều số điện thoại dạng:
01655 833 879. Chị ui e muốn lấy áo phao dáng dài màu đen ạ.
Gửi giúp e về địa chỉ 87 Nguyễn Trãi tphd.01683.603.783

Nếu chỉ có thêm 2 trường hợp như vậy có thể viết:

Mã:

Sub a()
Dim str As String
str = [a1]
With CreateObject("vbscript.regexp")
    .Global = True: .Pattern = "\d[\d\s\.]{8,}\d"
    If .test(str) Then MsgBox .Execute(str)(0)
End With
End Sub

Áp dụng vào bài này có thể bị sai một số trường hợp!!

HieuCD · 9/1/18

moctriet đã viết:
Cái trên của mình gợi ý rồi đó bạn. Nó còn cover được cả trường hợp trước đó người nhập nhầm số nào đó vào như thêm số 1, A1, A10 gì đó.

Mình không hình dung được liệt kê đầu số 08.... như thế nào
Dạng: 320.16kg. Cao 1m53.01692974417 thì kết quả sẽ như thế nào

moctriet · 9/1/18

Bạn xem trong hình

excel_lv1.5 · 9/1/18

moctriet đã viết:
Mình gợi ý cách đa lựa chọn hơn. Cách dưới mình dùng sakura để kiếm tra, lười viết trên regex của vba
Bước 1: Thay thế hết toàn bộ ký tự không phải số
(?!\r\n)\D

Bước 2: Lọc các số theo đầu số nhà mạng
(086|096|097|098|0162|0163|0164|0165|0166|0167|0168|0169|090|093|0120|0121|0122|0126|0128|091|094|0123|0124|0125|0127|0129|092|0188|0186)\d{7}

Với cách này có thể lọc ra theo từng nhà mạng mobi, vina, viettel, vietnam mobile. Cũng có thể viết được bằng công thức trên excel.

Nếu viết theo số đầu như thế thì bạn phải viết luôn số đầu của các máy bàn!!!

moctriet · 9/1/18

excel_lv1.5 đã viết:
Nếu viết theo số đầu như thế thì bạn phải viết luôn số đầu của các máy bàn!!!

Nếu bạn code cover nhiều trường hợp thì bạn phải chạy nhiều xử lý.
1. Xử lý dữ liệu thô: Đưa ra format phù hợp cho xử lý dữ liệu
2. Xử lý lấy các số dạng chuẩn (đơn giản). Để nếu không có ngoại lệ bớt được code.
3. Các dữ liệu không dạng chuẩn đưa ra xử lý tiếp. Lọc lấy các số >7 chữ số để kiểm tra
4. Các dữ liệu đã get được từ bước 3 sẽ xử lý dựa trên đầu số (có thể trả về kết quả đầu mạng để tiện gọi đỡ tốn kém chẳng hạn)
5. Các dữ liệu xử lý lỗi ở bước 4 sẽ đưa thêm xử lý phụ nào đó tùy nhu cầu. Hoặc báo không có.

Với máy bàn, hay mobile thì tùy theo nó thực sự có cần thiết không bạn có thể đưa thêm xử lý.

HieuCD · 9/1/18

excel_lv1.5 đã viết:
Nếu chỉ có thêm 2 trường hợp như vậy có thể viết:

Mã:

Sub a() Dim str As String str = [a1] With CreateObject("vbscript.regexp") .Global = True: .Pattern = "\d[\d\s\.]{8,}\d" If .test(str) Then MsgBox .Execute(str)(0) End With End Sub

Áp dụng vào bài này có thể bị sai một số trường hợp!!

Nhiều dữ liệu dạng "A300 01633886226 1c nha"
Đúng là rắc rối

HieuCD · 9/1/18

moctriet đã viết:
Bạn xem trong hình
View attachment 189654

Mình không biết cách thử như bạn, bạn thử với dạng nầy xem được không? có khoảng trống trong số điện thoại:
Dạng: 320.16kg. Cao 1m53.0169 2974 417 thì kết quả sẽ như thế nào

Anh chị giúp e tìm số điện thoại trong excel

Thành viên tích cực

Thành viên thường trực

Biên Hòa, Đồng Nai

File đính kèm

Thành viên tích cực

Thành viên thường trực

Thành viên thường trực

Chuyên gia GPE

Biên Hòa, Đồng Nai

Thành viên thường trực

Biên Hòa, Đồng Nai

Chuyên gia GPE

Thành viên thường trực

Biên Hòa, Đồng Nai

Thành viên tích cực

Chuyên gia GPE

Thành viên thường trực

Thành viên tích cực

Thành viên thường trực

Chuyên gia GPE

Chuyên gia GPE

Facebook

Group