Split Function in Excel VBA Macro

Excel VBA Macro code.

I am passing two sets of value to the variable strRecord for the below function.

Value1 :-  "1,1,,EACH,ADVAIR ,NDC#:0000-0695-00,STR:250-50MCG,FM:ARIN,SZ:60 EA,3163219,,6/30/2013,0,$100.20 ,$100.20 "

Using split function based on Comma, I am able to filter values and assign to its variable - DetaiFields

Value 2:  "4,4,,EACH,ATRIPLA,NDC#:15584-0101-01,STR:200-300-600MG,FM:TABS,SZ:30 EA,3743309,,6/30/2013,0,$785.83 ,$3,143.32 "

In the Value 2 for the last set is $3,143.22 ( Currency DataType ). I want the whole value 3143.32 . But the Split function filters by comma and retrieve only the value 3.

How to handle the Currency datatype value ?


Private Function GetInvoiceDetail(strRecord As String) As InvoiceDetail

    Dim DetailFields() As String
    DetailFields = Split(Replace(strRecord, Chr(34), ""), ",")
 
    GetInvoiceDetail.Ship_Qty = CLng(DetailFields(0))
    GetInvoiceDetail.Order_Qty = CLng(DetailFields(1))
    GetInvoiceDetail.Code = DetailFields(2)
    GetInvoiceDetail.UM = DetailFields(3)
    GetInvoiceDetail.Blank1 = DetailFields(4)
    GetInvoiceDetail.Description = DetailFields(5)
    GetInvoiceDetail.Blank2 = DetailFields(6)
    GetInvoiceDetail.Blank3 = DetailFields(7)
    GetInvoiceDetail.Blank4 = DetailFields(8)
    GetInvoiceDetail.CIN = CLng(DetailFields(9))
    GetInvoiceDetail.PO_Number = DetailFields(10)
    GetInvoiceDetail.Due_Date = CDate(DetailFields(11))
    GetInvoiceDetail.DEA_Class = CLng(DetailFields(12))
    GetInvoiceDetail.Unit_Price = CDbl(DetailFields(13))
    GetInvoiceDetail.Extension = CDbl(DetailFields(14))
    
End Function

Open in new window

chokkaStudentAsked:
Who is Participating?
 
Martin LissOlder than dirtCommented:
Dim intIndex As Integer
Dim intPosComma As Integer
Dim intPosEnd As Integer
Dim strTemp As String

Dim DetailFields() As String

    For intIndex = 1 To Len(strRecord)
        If Mid(strRecord, intIndex, 1) = "$" Then
            intPosEnd = InStr(intIndex, strRecord, ".")
            strTemp = Mid(strRecord, intIndex, intPosEnd - intIndex)
            strTemp = Replace(strTemp, ",", "|")
            strRecord = Left(strRecord, intIndex - 1) & strTemp & Mid(strRecord, intPosEnd)
        End If
    Next
    
    DetailFields = Split(Replace(strRecord, Chr(34), ""), ",")
    DetailFields(13) = Replace(DetailFields(13), "|", ",")
    DetailFields(14) = Replace(DetailFields(13), "|", ",")
 
    GetInvoiceDetail.Ship_Qty = CLng(DetailFields(0))
    GetInvoiceDetail.Order_Qty = CLng(DetailFields(1))
    GetInvoiceDetail.Code = DetailFields(2)
    GetInvoiceDetail.UM = DetailFields(3)
    GetInvoiceDetail.Blank1 = DetailFields(4)
    GetInvoiceDetail.Description = DetailFields(5)
    GetInvoiceDetail.Blank2 = DetailFields(6)
    GetInvoiceDetail.Blank3 = DetailFields(7)
    GetInvoiceDetail.Blank4 = DetailFields(8)
    GetInvoiceDetail.CIN = CLng(DetailFields(9))
    GetInvoiceDetail.PO_Number = DetailFields(10)
    GetInvoiceDetail.Due_Date = CDate(DetailFields(11))
    GetInvoiceDetail.DEA_Class = CLng(DetailFields(12))
    GetInvoiceDetail.Unit_Price = CDbl(DetailFields(13))
    GetInvoiceDetail.Extension = CDbl(DetailFields(14))

Open in new window

0
 
[ fanpages ]IT Services ConsultantCommented:
Hi,

Is the Unit_Price ever going to be greater than $999.99?

If not, you could use these changes to the last two lines;

GetInvoiceDetail.Unit_Price = CDbl(Replace(DetailFields(13), "$", ""))

GetInvoiceDetail.Extension = CDbl(Replace(DetailFields(14), "$", "") & IIf(UBound(DetailFields) = 15, DetailFields(15), ""))

Open in new window


BFN,

fp.
0
 
chokkaStudentAuthor Commented:
Thanks fanpage,

we need If Else Condition

Price can be $0 and it can be 3 to 5 digits.

In my posting , i have passed Value 1 and Value 2.  In the Value 1, i have mentioned with 3 digits and in the Value 2 i have mentioned with 4 digits.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Martin LissOlder than dirtCommented:
My code works for any length data and it handles both unit price and extension.
0
 
chokkaStudentAuthor Commented:
@MartinLiss - there is a problem. You can check by executing this attachment.

What happens is that Extension Field Value is replaced by Unit Price Value.
ImportCSV.xls
Test-6619289--5-30-2013.csv
0
 
chokkaStudentAuthor Commented:
@MartinLiss it works.

Thanks

 DetailFields(13) = Replace(DetailFields(13), "|", ",")
[b]    DetailFields(14) = Replace(DetailFields(14), "|", ",")[/b]

Open in new window

0
 
Martin LissOlder than dirtCommented:
Oops. Change line 19 to

 DetailFields(14) = Replace(DetailFields(14), "|", ",")
0
 
Martin LissOlder than dirtCommented:
I see you fixed the typo without my help:)
0
 
chokkaStudentAuthor Commented:
thank you MartinLiss
0
 
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.