Bài viết: Chiêu 28: Trích xuất dữ liệu số trong 1 chuỗi bằng VBA (2 người xem)

[ptm0412]

Chiêu 28: Trích xuất dữ liệu số trong 1 chuỗi bằng VBA

Bạn thường lấy dữ liệu từ nguồn ngoài, chẳng hạn từ Internet, trong đó có dữ liệu số xen lẫn chữ như: "1,254.00VND” hoặc "USD 2,500.00”, thậm chí còn phức tạp hơn.
Và cũng có khi bạn đã nhập liệu hỗn hợp text và số không theo quy luật nào để có thể lấy riêng số ra bằng các hàm tách chuỗi thông thường.
Dùng 1 hàm tự tạo viết bằng VBA, bạn có thể trích xuất riêng phần số ra, dù cho chuỗi có kiểu dạng gì đi nữa.
Bạn hãy nhấn Alt-F11 để vào cửa sổ VBA, insert 1 module và dán đoạn code sau vào:

Function ExtractNumber(rCell As Range)
Dim lCount As Long
Dim sText As String
Dim lNum As String
sText = rCell
For lCount = Len(sText) To 1 Step -1
If IsNumeric(Mid(sText, lCount, 1)) Then
lNum = Mid(sText, lCount, 1) & lNum
End If
Next lCount
ExtractNumber = CLng(lNum)
End Function

Vào lại Excel, trong ô B1 gõ dữ liệu số xen lẫn text tuỳ ý, trong ô kế bên C1 gõ công thức:
=ExtractNumber(B1)
Ta sẽ có kết quả như hình:

Bổ sung:

Vẫn còn chút vấn đề: nếu dữ liệu là số thập phân như ô B5, hoặc dữ liệu gồm 2 nhóm số riêng biệt trở lên như ô B4, kết quả sẽ không như ý muốn.

1. Để giải quyết vấn đề số thập phân, ptm0412 có 1 hàm khác:

Function CtoN(Mystr As String, Optional Dautp As String) As Double
Dim Kqng, Kqtp, Neg  As Double, Kqtam As String
Dim Sotp As Double, Le As Byte
Neg = 1
Le = 0
For i = 1 To Len(Mystr)
    tam = Mid(Mystr, i, 1)
    Select Case tam
        Case 0 To 9
            Kqtam = Kqtam & tam
        Case "-"
            Neg = -1
        Case Dautp
            Kqng = Kqtam
            Le = 1
            Mystr = Right(Mystr, Len(Mystr) - i)
            Kqtp = CtoN(Mystr)
            Sotp = Kqtp * 10 ^ (-Len(Kqtp))
        End Select
Next i
Select Case Le
Case 0
CtoN = IIf(Kqtam = "", 0, Kqtam)
Case 1
CtoN = Kqng + Sotp
End Select
CtoN = CtoN * Neg
End Function

Ghi chú:- Khi sử dụng hàm này, bạn sẽ thêm vào hàm 1 tham số cho biết dấu thập phân là dấu nào, ",” hay ".”. Thí dụ =CtoN("USD 14255.20”,”.”), và nếu bạn biết chắc là số nguyên thì không cần thêm.
- Hàm này đọc được cả số âm nếu ký hiệu số âm là dấu trừ và đứng trước số.

2. Để giải quyết vấn đề nhiều nhóm số khác nhau trong chuỗi, Ptm0412 cũng có 1 hàm:

 Function CtoNPlus(Mystr As String, sttchuoi As Byte, Optional Dautp As String) As Double
Newstr = Mystr
For i = 1 To sttchuoi
If Len(Newstr) < 2 Then Exit For
CtoNPlus = CtoN1st(Newstr, Dautp)
Next i
Newstr = ""
End Function

Function CtoN1st(ByVal Mystr As String, Optional Dautp As String) As Double
Dim Kqng, Kqtp, Neg  As Double, Kqtam As String
Dim Sotp As Double, Le As Byte, NewStr2 As String
Neg = 1
Le = 0
For i = 1 To Len(Mystr)
    tam = Mid(Mystr, i, 1)
    Select Case tam
        Case 0 To 9
            Kqtam = Kqtam & tam
        If IsNumeric(Mid(Mystr, i + 1, 1)) = False And _
        Mid(Mystr, i + 1, 1) <> "," And Mid(Mystr, i + 1, 1) <> "." Then
      Newstr = Right(Mystr, Len(Mystr) - i)

        Exit For
        End If
        Case "-"
            Neg = -1
        Case Dautp
            Kqng = Kqtam
            Le = 1
            NewStr2 = Right(Mystr, Len(Mystr) - i)
            Kqtp = CtoN1st(NewStr2)
            Sotp = Kqtp * 10 ^ (-Len(Kqtp))

        End Select
Next i
Select Case Le
Case 0
CtoN1st = IIf(Kqtam = "", 0, Kqtam)
Case 1

CtoN1st = Kqng + Sotp
End Select
CtoN1st = CtoN1st * Neg
End Function

Cú pháp hàm: CtoNPlus(Mystr , sttchuoi, [Dautp])
Sttchuoi là số thứ tự nhóm số trong chuỗi, Dautp là ký tự dấu phân cách thập phân.

Xem file kèm theo.​

MỤC LỤC Những tuyệt chiêu trong Excel
​

Một số bài viết có liên quan:
1/ Chiêu thứ 27: Chuyển đổi con số dạng văn bản sang số thực
2/ Chiêu 26: Sử dụng chức năng thay thế (Replace) để gỡ bỏ các ký tự không mong muốn
3/ Chiêu thứ 25: Tạo một danh sách xác thực thay đổi theo sự lựa chọn từ một danh sách khác
4/ Chiêu số 24: Dùng nhiều List cho 1 Combobox
5/ Chiêu số 23: Bật, tắt chức năng Conditional Formatting bằng 1 checkbox
6/ Chiêu thứ 22: Tạo hiệu ứng 3D trong các bảng tính hay các ô
7/ Chiêu thứ 21: Tô màu dòng xen kẽ
8/ Chiêu thứ 20: Đếm hoặc cộng những ô đã được định dạng có điều kiện
9/ Chiêu số 19: Đánh dấu những ô chứa công thức bằng Conditional Formatting
10/ Chiêu số 18: Điều khiển Conditional Formating bằng checkbox
http://www.giaiphapexcel.com/vbb/content.php?326

 

[HUYBOY]

Em cảm ơn, hay quá ạ

 

[cantl]

Bác ptm có lắm tuyệt chiêu quá. Chia làm nhiều trường phái: chiêu, chiêu số, chiêu thứ. Và có đặc điểm chung là các chiêu đã bị thất truyền (đứt link).

 

[ptm0412]

bị thất truyền (đứt link)

Đọc bài gốc ở đây, những bài này là 1 admin xưa kia copy thành từng bài riêng rẽ thuở vbulletin, chuyển sang Xenforo bị lỗi link

Tổng hợp những "tuyệt chiêu" trong Excel

Chào các bạn, Sau đây là danh sách tổng hợp những "tuyệt chiêu" trong Excel mà mọi người nên xem qua. Lời nói đầu Tùy biến cửa sổ làm việc của bảng tính Nhập dữ liệu đồng thời vào nhiều sheet Ngăn chận người sử dụng thực hiện một số hành động nào đó nhất định Ngăn chặn các nhắc nhở không cần...

www.giaiphapexcel.com