Parsing for data in between two characters

[rawdata]

I have a string of data that comes from another file that looks like the following:

$67.551($145.90)$0.00($78.35)$0.00

What I need to do is pull out the first, second, and fourth figures as in the following:

$67.55
($145.90)
($78.35)

As you can see a one-digit number comes after the first figure. In this case, the number 1 comes after the first figure, which needs to be ignored:

$67.551

In addition, the Second and Fourth figures may or may not be negative numbers.

Currently, I'm using the InStr function in Access, but I cannot get it to work in order to pull out these figures.



Any ideas are greatly appreciated.

Thanks.

[dcrake]

Are the () an indication of a negative number?
will the 1,2 & 4 figures always have a value (Not $0.00)?
Will the 3 & 5 values always be $0.00?
Will the extra digit always be a 1?

[rawdata]

1. Yes, () is an indication of a negative number
2. Not necessarily. Any of the desired figures (first, second, or fourth) may be negative numbers.
3. The 3rd and 5th figures may not necessarily be $0.00

[dcrake]

Have you any influence on how you are passed the string. It's just because a function would need to examine every single character then determine what to do next. There is no simple way to split the string up. Wouldn't be so bad if there was a space between each value.

In theory you would have to step through looking for decimal points, then back track until you found a $ then back one more to see if it is a ( then skip forward to 2 places after the decimal point then check for a ) after the 2nd decimal.

Once you have done that for the first value you would then copy that to a variable and strip off the front of the string, then repeat the above 5 times.

David

[rawdata]

the data is scraped from a website using vba. I'd be happy to entertain any excel tips on parsing the data, as well.

[dcrake]

Have you any control on how the data is scraped or access to the vba code that does it?

If someone is providing this information for you then I imagine there is a cost involved, if so, tell them that the scrape needs revising as it is not fit for purpose.

David

[rawdata]

If I had no other choice, do you have ideas around the formulas that I would use to parse the data in several steps inside Access?

[dcrake]

sstr = IncomingString

'Where is the first . (dot)

 
iDot = InStr(sstr,".")
iDollar = InStr(sstr,"$")
 
Dollars = Mid(sstr,iDollar+1,iDot-1)
Cents = Mid(sstr,iDot+1,2)
 
'Test for negative 
If Mid(sstr,iDollar-1) = "(" Then
 
   Amount = "(" & Dollars & "." & Cents & ")"
else
   Amount = Dollars & "." & Cents 
 
End if
'Cut of the calculation
sstr = mid(sstr,Len(Amount))
 
Amount = CCur(Amount)

This code extracts the FIRST value then Cuts that off the begginning of the string you would have to repeat this five times in a loop but only worry about the ones you want.


EDIT
If the first value is a negative is the extra digit inside or outside the closing bracket

($67.551) or ($67.55)1

This is aircode and as such untested.

David