Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Split Function in Excel VBA Macro

Posted on 2013-06-26
10
798 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 46

Accepted Solution

by:
Martin Liss earned 500 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 46

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 46

Expert Comment

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

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

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 46

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
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!
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

808 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