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:
PHP Code:
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:
PHP Code:
Comment [1]:
BEGIN TEMPLATE:
bbcode_php
Comment [2]:
END TEMPLATE:
bbcode_php
Comment [3]:
BEGIN TEMPLATE:
bbcode_php
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:
PHP Code:
Comment [4]:
END TEMPLATE:
bbcode_php
Comment [5]:
BEGIN TEMPLATE:
bbcode_php
Function CtoNPlus(Mystr As String, sttchuoi As Byte, Optional Dautp A
s 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
PHP Code:
Function CtoN1st(ByVal Mystr As String, Optional Dautp As String) As D
ouble
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) <> "." Th
en
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
Comment [6]:
END TEMPLATE:
bbcode_php
Comment [7]:
BEGIN TEMPLATE:
bbcode_php
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.
Comment [8]:
END TEMPLATE:
bbcode_php