lọc số và ký tự trong cùng một chuỗi?

[LikeIt]

Gửi các anh chị,

Liên quan đến chủ đề trích lọc dữ liệu, em có đọc một số bài trên GPE nhưng chưa thấy có cách lọc cả số và ký tự, em có một file đính kèm trong đó có mô tả hàng hóa, hiện em đang cần lọc cả số và chữ như trong cột C file đính kèm, mong các anh chị chỉ giáo. tks / NHT

 

[anhtuan1066]

Em cho hỏi, có phải tổng công trong file có 3 loại đơn vị tính ko? Là g, s va ml ???
ANH TUẤN

 

[ptm0412]

Loc số thì anh có, cả số và dvtính thì không. Tuy nhiên có thể tách dvtính ra và lookup theo 1 DS có sẵn, hoặc DS tự tạo bằng Remove Duplicate trong Excel2007, em đang xài 2007 mà.
Xem file, hàm tự tạo CtoN1st(chuỗi, dấu thập phân)

 

[Mr Okebab]

Loc số thì anh có, cả số và dvtính thì không. Tuy nhiên có thể tách dvtính ra và lookup theo 1 DS có sẵn, hoặc DS tự tạo bằng Remove Duplicate trong Excel2007, em đang xài 2007 mà.
Xem file, hàm tự tạo CtoN1st(chuỗi, dấu thập phân)

Đã làm VBA thì cho ra thành 1 hàm luôn bác ạ, nó sẽ tách ra số và đơn vị tính luôn.

Để biết đâu là đơn vị tính thì ta sẽ có một bảng danh mục đơn vị tính (chứ không phải là danh mục sản phẩm), các ký tự liền kề với số nếu có mặt trong bảng danh mục đơn vị tính sẽ được lấy ra.

Thân!

 

[ptm0412]

To MrOKBAB:
Mình tính viết 1 hàm có thể dùng chung cho nhiều người và nhiều kiểu dữ liệu, nhưng chưa xong nên đánh số 1st, có thể mình cải tiến 2nd đọc số thứ nhì trong chuỗi
thí dụ: "JOHNY WALKER 650ML X 24BOTTLES" mình sẽ tách riêng số 650 và 24.
Đa số người dùng sẽ còn muốn tính toán trên 2 số này, nếu kèm dvtính thì không tính toán được.
Tuy nhiên trong trường hợp cụ thể này, nếu Hương Thơm cần thì mình tách thêm dvtính cũng được, cải tiến theo gợi ý của bạn. Hẹn tối nay.
Thanks

 

[ptm0412]

Thêm hàm

Y hẹn một nửa.
- Đã có CtoN2nd tách số thứ nhì 24.
- CtoNUnit tách cả số kèm đơn vị tính đã có nhưng còn lỗi: nếu số nguyên (400ML) thì đúng, nhưng số thập phân (6.5 ML) thì sai: 6.05ML

nhờ MrOKeBab coi hộ chỗ này:

 Code:
Kqtp = CtoNUnit(Mystr, Dautp)
' Kqtp đang có giá trị " 5ML" '
Sotp = Val(Kqtp) * 10 ^ (-Len(Str(Val(Kqtp))))
' Val(Kqtp) có giá trị 5 '
[left]' nhưng len(str(val(Kqtp))) lại có giá trị 2 ' [/left]

Trừ len(str(val(Kqtp))) cho 1 thì được rồi nhưng sợ không phải là đúng cho mọi trường hợp, trừ khi mình biết chắc lý do.​

 

[ptm0412]

Đã có hướng giải quyết, nhưng là kết hợp 2 hàm CtoN1st và CtoNUnit. Được 2 em hoàn chỉnh.
- 1 cái sửa hàm hôm qua, cho Kqtp là số khỏi str(val()), len(Kqtp) bằng đúng giá trị mong muốn
- 1 cái ngắn gọi CtoN1st từ đầu, lấy số, thêm dvt

File kèm theo định dạng 2003 theo tình hình số đông chưa có 2007. Cám ơn Minhlev quan tâm và nhắc nhở.

 

[Mr Okebab]

Đã có hướng giải quyết, nhưng là kết hợp 2 hàm CtoN1st và CtoNUnit. Được 2 em hoàn chỉnh.
- 1 cái sửa hàm hôm qua, cho Kqtp là số khỏi str(val()), len(Kqtp) bằng đúng giá trị mong muốn
- 1 cái ngắn gọi CtoN1st từ đầu, lấy số, thêm dvt

File kèm theo định dạng 2003 theo tình hình số đông chưa có 2007. Cám ơn Minhlev quan tâm và nhắc nhở.

Em mạo muội góp vui xíu:
- Xét lần lượt các ký tự của chuỗi
- Lấy tất cả các số gần nhau
- Xét xem các ký tự phía sau số có phải là ĐVT giống như trong Danh Sách hay không. Nếu đúng thì lấy (thoát), còn nếu không thì xét tiếp.

--> Số và đơn vị tính. Số và đơn vị tính cách nhau 1 khoảng trắng để phục vụ cho việc bóc tách sau này.

Function CVE(Chuoi As Range, DS As Range) As String
    On Error Resume Next
    Application.Volatile (False)
    If Len(Chuoi) < 2 Then Exit Function
    Dim i As Integer, i1 As Integer
    Dim Kytu As String, Kytu1 As String, TempDV As String
    For i = 1 To Len(Chuoi)
        Kytu = Mid$(Chuoi, i, 1)
        If IsNumeric(Kytu) = True Then
            For i1 = i + 1 To Len(Chuoi)
            Kytu1 = Mid$(Chuoi, i1, 1)
                If IsNumeric(Kytu1) = False And Kytu1 <> "." And Kytu1 <> "," Then
                    TempDV = TempDV & Mid$(Chuoi, i1, 1)
                    If WorksheetFunction.CountIf(DS, Trim(TempDV)) > 0 Then GoTo TimThay
                End If
            Next
        End If
    Next
    Exit Function
TimThay:
    CVE = Trim(Mid$(Chuoi, i, i1 - i + 1))
    CVE = Left$(CVE, Len(CVE) - Len(Trim(TempDV))) & " " & Trim(TempDV)
End Function

Mong được góp ý!!

Thân!

 

[ptm0412]

Cám ơn MrBab,
Code của bạn áp dụng thủ thụât rất hay, để mình giải thích xem đúng không nha:
- áp dụng 2 vòng lặp for lồng nhau
- khi đạt 1 mục tiêu cụ thể, chặn đứng vòng lặp ngoài, chạy vòng lặp trong.
- Khi đạt mục tiêu của vòng lặp trong, đi thẳng ra ngoài 2 cả vòng lặp.

Mục đích là sử dụng giá trị 2 biến i và i1 của 2 vòng lặp (tại thời điểm bị dừng) cho việc tính kết quả.

Ngâm cứu nửa tiếng mới hiểu lờ mờ thủ thuật này đấy.

--> Số và đơn vị tính. Số và đơn vị tính cách nhau 1 khoảng trắng để phục vụ cho việc bóc tách sau này.

Bóc tách ra chưa chắc có thể tính toán được: Giả sử số thập phân trong Excel quy định là phẩy, mà chuỗi tách ra có dấu chấm.

Còn hàm của mình dài hơn do mình có chủ định khác:
- Tách từng chuỗi số ra khỏi 1 chuỗi phức tạp gồm từ 2 nhóm số trở lên, có cả dấu thập phân cả 2 kiểu phẩy và chấm, kèm cả dấu trừ nếu âm.
- tách không kèm đơn vị tính nhằm mục đích tính toán cho các kết quả.
- ở bài trước mình tách được 2 chuỗi số, nay mình thực hiện được với chuỗi bất kỳ với bao nhiêu nhóm số cũng được.
- thủ thuật là đệ quy, 1 lần hoặc nhiều lần.

Mình đã làm 1 hàm duy nhất, tách chuỗi phức tạp thành nhiều nhóm số.
Trong file kèm theo mình thử với chuỗi gồm 5 nhóm số như chuỗi:
"400mlX24bottle And450mlX20bottle And2100mlDeGoDen"
kết quả là: 400 | 24 | 450 | 20 | 2100

Nhưng hiện giờ mới đúng cho số nguyên âm và dương, chưa đúng cho số thập phân. Nhờ MrOkeBab và các cao thủ góp ý xem sai chỗ nào.

Còn câu hỏi bài trước mình ngẫm thấy val("5ML") = 5 nhưng len(str(val("5ML"))) = 2 là không biết tại sao có phải quy luật không, muốn cho chắc thì len(trim(str(val("5ML")))) sẽ bằng 1.

 

[Mr Okebab]

Cám ơn MrBab,
Code của bạn áp dụng thủ thụât rất hay, để mình giải thích xem đúng không nha:
- áp dụng 2 vòng lặp for lồng nhau
- khi đạt 1 mục tiêu cụ thể, chặn đứng vòng lặp ngoài, chạy vòng lặp trong.
- Khi đạt mục tiêu của vòng lặp trong, đi thẳng ra ngoài 2 cả vòng lặp.

Mục đích là sử dụng giá trị 2 biến i và i1 của 2 vòng lặp (tại thời điểm bị dừng) cho việc tính kết quả.

Ngâm cứu nửa tiếng mới hiểu lờ mờ thủ thuật này đấy.

Bóc tách ra chưa chắc có thể tính toán được: Giả sử số thập phân trong Excel quy định là phẩy, mà chuỗi tách ra có dấu chấm.

Còn hàm của mình dài hơn do mình có chủ định khác:
- Tách từng chuỗi số ra khỏi 1 chuỗi phức tạp gồm từ 2 nhóm số trở lên, có cả dấu thập phân cả 2 kiểu phẩy và chấm, kèm cả dấu trừ nếu âm.
- tách không kèm đơn vị tính nhằm mục đích tính toán cho các kết quả.
- ở bài trước mình tách được 2 chuỗi số, nay mình thực hiện được với chuỗi bất kỳ với bao nhiêu nhóm số cũng được.
- thủ thuật là đệ quy, 1 lần hoặc nhiều lần.

Mình đã làm 1 hàm duy nhất, tách chuỗi phức tạp thành nhiều nhóm số.
Trong file kèm theo mình thử với chuỗi gồm 5 nhóm số như chuỗi:
"400mlX24bottle And450mlX20bottle And2100mlDeGoDen"
kết quả là: 400 | 24 | 450 | 20 | 2100

Nhưng hiện giờ mới đúng cho số nguyên âm và dương, chưa đúng cho số thập phân. Nhờ MrOkeBab và các cao thủ góp ý xem sai chỗ nào.

Còn câu hỏi bài trước mình ngẫm thấy val("5ML") = 5 nhưng len(str(val("5ML"))) = 2 là không biết tại sao có phải quy luật không, muốn cho chắc thì len(trim(str(val("5ML")))) sẽ bằng 1.

Bác thân!

Việc biến ký tự "." hoặc "," thành dấu thập phân thì không khó , chỉ cần nhờ

Application.ThousandsSeparator

là ta biết được dấu thập phân là cái gì để biến đổi các ký tự trên thành dấu đó trong TH đã tìm được chuỗi số như ý.

Việc bóc tách là cần thiết, vì vậy em cho có dấu cách giữa số và đơn vị tính để sau này muốn bóc tách thì ta có thể dùng các hàm đơn giản để bóc tách (vì có quy luật cụ thể)

VD của em chỉ là VD để giải quyết câu hỏi của bạn H.Thơm thôi, còn trong TH nhiều như của bác thì lại thêm mấy vòng lặp nữa thôi ạ, không thoát nữa mà duyệt đến hết chuỗi luôn.
Còn các vấn đề khác thì em sẽ nghiên cứu.

Cảm ơn bác nhiều!
Thân!

 

[ptm0412]

Đã làm xong CtoNPlus(chuỗi, dấu TP, Stt chuỗi số) dùng tách chuỗi phức tạp có nhiều nhóm số (bất kỳ).
Thí dụ: chuỗi:
"650mlX24bottle And50mlX120Smallbottle And1.000mlX12VaseDeGoden"
gồm 6 nhóm số, sẽ tách ra thành:
650 | 24 | 50 | 120 | 1000 | 12
hàm có thể đọc số thập phân định dạng phẩy hoặc chấm, có hoặc không có dấu phân cách hàng ngàn, đọc số âm định dạng -xxx
Đọc số thập phân, cho dấu thập phân vào tham số thứ 2.
Đọc số thứ mấy trong chuỗi, cho số thứ tự nhóm số vào tham số thứ 3.

Đố MrOkeBab xem đã sửa những gì từ file CtoNPlusPtm1.xls!

 

[minhlev]

Công thức của bác ptm0412 thật là hay. Tuy nhiên theo em, khi tính toán trong bảng tính, bác nên sử dụng 1 công thức CtoNPlus(chuỗi, dấu TP, Stt chuỗi số) thôi (kể cả tìm dãy số đầu tiên). Để thực hiện được như vậy thì trong đoạn code viết cho Function CtoNPlus cần chỉnh lại một chút ở đoạn sau:

If sttchuoi = 1 Then
CtoNPlus = so1

thành

If sttchuoi = 1 Then
soN = so1

Hoặc đưa dòng code: CtoNPlus = soN lên trước dòng End If cuối cùng của Function CtoNPlus

 

[ptm0412]

Chính xác là như vầy:

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

Hàm phải chạy 1 lần cho chuỗi gốc MyStr để có được NewStr =Right(MyStr, ...)
Các lần sau chạy cho NewStr, mỗi lần chạy, NewStr thay đổi.

Mình đã sửa như vậy trong sở làm, nhưng không chép về, nên phải làm lại ở nhà cho phần chính bị lỗi, phần này không bị lỗi nên quên.

Tuy nhiên theo em, khi tính toán trong bảng tính, bác nên sử dụng 1 công thức CtoNPlus(chuỗi, dấu TP, Stt chuỗi số) thôi (kể cả tìm dãy số đầu tiên).

Mình chỉ dùng 1 hàm đấy chứ?
Chuỗi 1 = B1 = CtoNPlus(A1;",";1)
Chuỗi 2 = C1 = CtoNPlus(A1;",";2)
. . .
Chuỗi n = Celln1 = CtoNPlus(A1;",";n)

 

[minhlev]

Theo em, cái đoạn code của Function CtoN1st mới là cái khó còn code của Function CtoNPlus chỉ là cái ứng dụng phía sau mà thôi. Tuy nhiên, mạn phép bác ptm0412, em xin được chỉnh lại code của Function CtoNPlus như sau:

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
        CtoNPlus = 0
        Exit For
    Else:    CtoNPlus = CtoN1st(Newstr, Dautp)
    End If
Next i
Newstr = ""
End Function

Lúc này công thức có dạng là:
CtoNPlus(chuỗi, Stt chuỗi số, dấu TP)
Nhờ bác ptm0412 và các bác trong diễn đàn kiểm tra lại xem có gì sai sót không???

 

[ptm0412]

Đúng rồi minhlev ạ.
- Đưa tham số sttchuoi ra trước bỏ được lựa chọn optional, để sau bác Bill không cho.
- Newstr = Mystr hay đấy, rút gọn được hàm.
- Hàm CtoN1st chưa phải là tối ưu, mình chỉ làm được có thế. Tuy nhiên dù gì mình cũng mò ra được 1 thủ thuật hay, là gọi 1 hàm hoặc thủ tục (procedure) ra thực thi nhiều lần.
- Học thêm bác Bab thủ thuật dừng For lấy giá trị biến lặp.

 

[ptm0412]

Ừ thì cứ làm, làm để học hỏi chứ. Còn tác giả thì . . .
Cũng như cái file tinh.xls của bạn mới làm đấy, nó ở 3 topic khác nhau, mình cũng tham gia vào 2 cái, trả lời, hỏi thêm, giải thích, rồi biến mất. Topic thứ 3 vì bạn làm rồi nên thôi.
Mà dữ liệu đưa lên mỗi lần 1 khác nhau, yêu cầu khác nhau.
Với lại mình có nói dùng hàm Sumproduct vào là bị chậm, tác giả không nghe thì mình chịu thua.
Bài này là spam đây.

 

[nuinhan]

Mình cần đơn giản hơn nè, muốn tách từ 400ml => 400 thôi thì tách sao? làm mãi chẳng được. Tks,

 

[dat_butmuc]

Mình cần đơn giản hơn nè, muốn tách từ 400ml => 400 thôi thì tách sao? làm mãi chẳng được. Tks,

Bạn nên gửi file hay mô tả tất cả tất cả các trường hợp khác để các Cao thủ giúp một cách tổng thể nhất (nếu phát sinh số liệu/trường hợp khác...)
Còn bạn chỉ muốn 400ml => 400 thì chỉ cần =LEFT(A1,3) - cho 400ml tại Ô A1.

 

[sealand]

Mình tham gia hàm ngắn hơn và cho phép tuỳ chọn lọc lấy số hay ký tự abc.

Function Num(ch As String, Kieu As Byte) As Variant
For i = 1 To Len(ch)
If IsNumeric(Mid(ch, i, 1)) = IIf(Kieu = 1, True, False) Then Num = Num & Mid(ch, i, 1)
Next
If Kieu = 1 Then Num = Val(Num)
End Function

Cú pháp: = Num(Chuoi,Kieu)
Kieu=1 thì lấy số.
Kieu=0 lấy chữ.

 

[binhdien]

Mình dùng mấy hàm tách số ra khỏi chuỗi của bạn thì được rồi. Nhưng khi tách số thập phân thì bị vướng VD: Xuất kho 12,05 lít dầu thì nó cho kết quả là 12,5 chứ không phải là 12,05. Bạn có cách nào khấc phục giúp mình với.