[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Split Function in Excel VBA Macro

Posted on 2013-06-26
10
Medium Priority
?
869 Views
Last Modified: 2013-06-26
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

0
Comment
Question by:chokka
  • 5
  • 4
10 Comments
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39279159
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
 

Author Comment

by:chokka
ID: 39279188
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
 
LVL 51

Accepted Solution

by:
Martin Liss earned 2000 total points
ID: 39279197
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 51

Expert Comment

by:Martin Liss
ID: 39279204
My code works for any length data and it handles both unit price and extension.
0
 

Author Comment

by:chokka
ID: 39279230
@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
 

Author Comment

by:chokka
ID: 39279257
@MartinLiss it works.

Thanks

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

Open in new window

0
 
LVL 51

Expert Comment

by:Martin Liss
ID: 39279288
Oops. Change line 19 to

 DetailFields(14) = Replace(DetailFields(14), "|", ",")
0
 
LVL 51

Expert Comment

by:Martin Liss
ID: 39279297
I see you fixed the typo without my help:)
0
 

Author Closing Comment

by:chokka
ID: 39279420
thank you MartinLiss
0
 
LVL 51

Expert Comment

by:Martin Liss
ID: 39279425
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

608 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question