Extract Data

[momoko]

Hi,

Currently I have a remark field and want to just extract the amount. The data input is not in a structured manner therefore I could not use the mid function. Appreciate some advise.

Example :
Remark
CASH PURCHASE $50K.DEBIT 3007484701
SUB $20,000 FIRST STATE DIVIDEND
AMT:$10,900(FS BRIDGE FD)NO DISC

 

[macca the hacke]

Use the InStr function to search for "amt" - this will give you place of the "a" from "amt" in field. You could then use mid function to take everything from right of this place. Then use InStr again to search for "$". Finally use Mid function again to extract everything to right of $ symbol.

ie something along lines of:

Instr([remark],"amt") will give you position of "a" in [remark] field.

then use

Mid ([remark],b,c-b) where b is variable you have just got from above, and c is len([remark])

Hope this makes sense!

 

[neileg]

Try using the Val() function. This might give you what you want.

 

[Bat17]

Assuming that you are after the value after AMT:$ then

x = Your String
y = InStr(x, "AMT:$")
z = Val(Replace(Mid(x, y + 5), ",", ""))

HTH

Peter