hàm tách số khỏi chuỗi kí tự,...!

[conghung999]

ết hàm bang VB nhé:
1 . ALT + F11 -> Insert -> Module.
2. Dán đoạn code này vào khoảng trống nhé.

Function TS(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 = TS(Mystr)
Sotp = Kqtp * 10 ^ (-Len(Mystr))
End Select
Next i
Select Case Le
Case 0
TS = Kqtam
Case 1
TS = Kqng + Sotp
End Select
TS = TS * Neg
End Function

3. Chọn Files - > Save as -> save as type - > Micorossoft office excell add-ins ( *.xla) ở dưới cùng.-> lưu name = TS.
4. Tools -> add - Ins... -> TS -> ok.
5. khởi động lại excell.
chỉ cần gõ = TS(ô muốn tách số). là ok...!!!
Hàm này sẽ giúp ích rất nhiều trong làm sổ sách đấy...!!!!:thumbup::hurray::happy3:

View more random threads:

• đổi số ra chữ không cần lập trình
• Tặng file excel kế toán
• xin file excel theo QĐ 48 hình thức CTGS
• Xin phần mềm kế toán công ty sản xuất (phân bón)
• Có một phai excel nhờ mọi người cho ý kiến nhé
• Auto save trong Excel
• Sổ kế toán định dạng file excel
• Công thức tính ngày công thế nào?
• Xin dữ liệu excel kế toán mẫu thực tế theo qđ 15
• PM KT = Excel theo QĐ48 cập nhập theo T. tư mới 138 (244) của Tiger2774, Daniel đã sửa & bổ xung

[namtv91]

Ðề: hàm tách số khỏi chuỗi kí tự,...!

Bạn cho hỏi trong VB tách số trong cuỗi ký tự mà bạn hướng dẫn, mình muốn lấy cả ký tự số 0, thì phải làm sao: Ví dụ: Nguyen van a thanh toan cho A so TK 0123456789. KET QUA YEU CAU la: 0123456789

Bạn giúp mình nhé.

Thanhks!

[COLOR=#000000]
[/COLOR][COLOR=#007700]Function [/COLOR][COLOR=#0000BB]SplitNumber[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]strMyString [/COLOR][COLOR=#007700]As [/COLOR][COLOR=#0000BB]String[/COLOR][COLOR=#007700]) As [/COLOR][COLOR=#0000BB]String    Dim strResults [/COLOR][COLOR=#007700]As [/COLOR][COLOR=#0000BB]String[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]strTemp [/COLOR][COLOR=#007700]As [/COLOR][COLOR=#0000BB]String    Dim i [/COLOR][COLOR=#007700]As [/COLOR][COLOR=#0000BB]Integer    [/COLOR][COLOR=#007700]For [/COLOR][COLOR=#0000BB]i [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]1 To Len[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]strMyString[/COLOR][COLOR=#007700])        [/COLOR][COLOR=#0000BB]strTemp [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]Mid[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]strMyString[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]i[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]1[/COLOR][COLOR=#007700])        If [/COLOR][COLOR=#0000BB]IsNumeric[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]strTemp[/COLOR][COLOR=#007700]) = [/COLOR][COLOR=#0000BB]True Then            strResults [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]strResults [/COLOR][COLOR=#007700]& [/COLOR][COLOR=#0000BB]strTemp        End [/COLOR][COLOR=#007700]If    [/COLOR][COLOR=#0000BB]Next    SplitNumber [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]strResultsEnd [/COLOR][COLOR=#007700]Function[/COLOR] 

[phu1]

Ðề: hàm tách số khỏi chuỗi kí tự,...!

Nếu chỉ lấy hoàn toàn là số để tính toán thì sửa lại hàm trên như sau :

[COLOR=#000000]
[/COLOR][COLOR=#007700]Function [/COLOR][COLOR=#0000BB]SplitNumber[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]strMyString [/COLOR][COLOR=#007700]As [/COLOR][COLOR=#0000BB]String[/COLOR][COLOR=#007700]) As [/COLOR][COLOR=#0000BB]Double    Dim strResults [/COLOR][COLOR=#007700]As [/COLOR][COLOR=#0000BB]String[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]strTemp [/COLOR][COLOR=#007700]As [/COLOR][COLOR=#0000BB]String    Dim i [/COLOR][COLOR=#007700]As [/COLOR][COLOR=#0000BB]Integer    [/COLOR][COLOR=#007700]For [/COLOR][COLOR=#0000BB]i [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]1 To Len[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]strMyString[/COLOR][COLOR=#007700])        [/COLOR][COLOR=#0000BB]strTemp [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]Mid[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]strMyString[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]i[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]1[/COLOR][COLOR=#007700])        If [/COLOR][COLOR=#0000BB]IsNumeric[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]strTemp[/COLOR][COLOR=#007700]) = [/COLOR][COLOR=#0000BB]True Then            strResults [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]strResults [/COLOR][COLOR=#007700]& [/COLOR][COLOR=#0000BB]strTemp        End [/COLOR][COLOR=#007700]If    [/COLOR][COLOR=#0000BB]Next    SplitNumber [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]Val[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]strResults[/COLOR][COLOR=#007700])[/COLOR][COLOR=#0000BB]End [/COLOR][COLOR=#007700]Function[/COLOR] 

Thân mến

[phluant]

Ðề: hàm tách số khỏi chuỗi kí tự,...!

Muốn dùng thêm số lẻ thập phân nữa thì thế này

[COLOR=#000000]
[/COLOR][COLOR=#007700]Function [/COLOR][COLOR=#0000BB]SplitNumberDecimal[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]strMyString [/COLOR][COLOR=#007700]As [/COLOR][COLOR=#0000BB]String[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]Optional DecimalPoint [/COLOR][COLOR=#007700]As [/COLOR][COLOR=#0000BB]String[/COLOR][COLOR=#007700]) As [/COLOR][COLOR=#0000BB]Double    Dim strResults [/COLOR][COLOR=#007700]As [/COLOR][COLOR=#0000BB]String[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]strTemp [/COLOR][COLOR=#007700]As [/COLOR][COLOR=#0000BB]String    Dim intResultsIn [/COLOR][COLOR=#007700]As [/COLOR][COLOR=#0000BB]Double[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]intResultsDe [/COLOR][COLOR=#007700]As [/COLOR][COLOR=#0000BB]Double    Dim i [/COLOR][COLOR=#007700]As [/COLOR][COLOR=#0000BB]Integer[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]j [/COLOR][COLOR=#007700]As [/COLOR][COLOR=#0000BB]Integer[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]So [/COLOR][COLOR=#007700]As [/COLOR][COLOR=#0000BB]Integer    [/COLOR][COLOR=#007700]For [/COLOR][COLOR=#0000BB]i [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]1 To Len[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]strMyString[/COLOR][COLOR=#007700])        [/COLOR][COLOR=#0000BB]strTemp [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]Mid[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]strMyString[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]i[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]1[/COLOR][COLOR=#007700])        If [/COLOR][COLOR=#0000BB]IsNumeric[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]strTemp[/COLOR][COLOR=#007700]) = [/COLOR][COLOR=#0000BB]True [/COLOR][COLOR=#007700]Or [/COLOR][COLOR=#0000BB]strTemp [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]DecimalPoint Then            strResults [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]strResults [/COLOR][COLOR=#007700]& [/COLOR][COLOR=#0000BB]strTemp        End [/COLOR][COLOR=#007700]If    [/COLOR][COLOR=#0000BB]Next    [/COLOR][COLOR=#007700]For [/COLOR][COLOR=#0000BB]j [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]1 To Len[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]strResults[/COLOR][COLOR=#007700])        [/COLOR][COLOR=#0000BB]strTemp [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]Mid[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]strResults[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]j[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]1[/COLOR][COLOR=#007700])        If [/COLOR][COLOR=#0000BB]strTemp [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]DecimalPoint Then            intResultsIn [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]Val[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]Left[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]strResults[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]j [/COLOR][COLOR=#007700]- [/COLOR][COLOR=#0000BB]1[/COLOR][COLOR=#007700]))            [/COLOR][COLOR=#0000BB]So [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]Val[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]Len[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]strResults[/COLOR][COLOR=#007700]) - [/COLOR][COLOR=#0000BB]j[/COLOR][COLOR=#007700])            [/COLOR][COLOR=#0000BB]intResultsDe [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]10 [/COLOR][COLOR=#007700]^ (-[/COLOR][COLOR=#0000BB]So[/COLOR][COLOR=#007700]) * [/COLOR][COLOR=#0000BB]Val[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]Right[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]strResults[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]Len[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]strResults[/COLOR][COLOR=#007700]) - [/COLOR][COLOR=#0000BB]j[/COLOR][COLOR=#007700]))        Else            [/COLOR][COLOR=#0000BB]intResultsIn [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]Val[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]strResults[/COLOR][COLOR=#007700])            [/COLOR][COLOR=#0000BB]intResultsDe [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]0        End [/COLOR][COLOR=#007700]If    [/COLOR][COLOR=#0000BB]Next    SplitNumberDecimal [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]intResultsIn [/COLOR][COLOR=#007700]+ [/COLOR][COLOR=#0000BB]intResultsDeEnd [/COLOR][COLOR=#007700]Function[/COLOR] 

Thân mến