Need to Parse amount and date from string inside a field

[psbsmms]

I have a cell in a excell file that comes from a customer and I want to be able to pull the payment and the date out of the cell and then enter both in an access table.

the cell looks like this    

Awaiting Payment($1004.260::01/10/2009::0)

I can get the strat of the amount with the left funftion but how do i determine the limit since it cloud be of varing size?

 

[Remou]

In this case, I think split should suit:

s="Awaiting Payment($1004.260::01/10/2009::0)"
a=Split(mid(s,Instr(s,"$")),":")

a(0) is the amount
a(2) is the date

 

[kjv1611]

In Remou's example, the variable, a, is an array variable, string format:

Dim a() As String

Just in case you needed any clarification..

a(0) would be the first value of the array, a(1) = second value, a(2) = third value.

That really does sound like the best option for you - with the Split function, it does not matter what the length of each part is, so long as the split delimiter, "::" in this case, is constant.

--

"If to err is human, then I must be some kind of human!" -Me

 

[TheAceMan1]

How are ya kjv1611 . . .

For the split function, the array needs to be a [blue]Variant![/blue]

[blue] Dim a, s As String[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[RoyVidar]

> For the split function, the array needs to be a Variant!

No. As demonstrated in this MS sample *), it can very well be a string array

http://support.microsoft.com/kb/266289.

*) From the link; "Although the Microsoft Visual Basic product documentation describes how to use the Split function, the product documentation does not contain an example that uses the Split function." - so one might perhaps call this the "official MS Split sample".

As far as I know, there is no difference between the Classic VB Split and VBA Split, except for Access, where there's one more compare argument values, see

http://msdn.microsoft.com/en-us/library/aa263365(VS.60).aspx

Roy-Vidar