Instr? (1 Viewer)

[cfp76]

I have a column that returns data like this:

ABC~123456789~125.25~D~04-18-2013

I am trying to use INSTR to find the 2nd ~ in the string, which marks the FIRST digit in the dollar amount.... but how to I make it end at the end of the dollar amount (the next ~)

All other values are a fixed length but the dollar amount so I have been able to grab the others working from the right and left... but the dollar amount has me baffled since it can be up to 8 digits including the decimal.

 

[pr2-eugin]

cfp76, you can use the Split function to first split based on the pattern string "~", then you need only the third part so simply use the ArrayElement(2).. Something along the lines of..

Public Function getDollar(tmpStr As String) As Double
    Dim tmpArr() As String
    tmpArr = [URL="http://msdn.microsoft.com/en-gb/library/6x627e5f%28v=vs.80%29.aspx"]Split[/URL](tmpStr, "~")
    getDollar = [URL="http://www.techonthenet.com/access/functions/datatype/cdbl.php"]CDbl[/URL](tmpArr(2))
End Function

Usage would be simply..

? getDollar("ABC~123456789~125.25~D~04-18-2013")
 125.25

 

[cfp76]

When I use the SPLIT function in my query, it says that it unavailable....

 

[CJ_London]

I think Dave's solution is the better one, but if you want to use InStr for the 2nd ~ then the first parameter is a start position so if the first ~ is always position 4 use

Instr(5,"YourStr","~")