Remove text from field

[Guest]

Hi - and thanks for reading this!

I am trying to remove text from a field. Sometimes the field will contain
only numbers (and a decimal point) already, but a lot have a leading space
or a word and a space before the number - eg. Price £65.00. I would like to
be able to just leave 65, or 65.00. I need any numbers after the decimal
point (maximum of two digits) if they are not zeroes.

Thanks for your help.
Andy.

 

[Jerry Whittle]

One place to start would be with the Val() function. It will strip out the
leading spaces and any trailing spaces or alpha characters after the number.
But if it runs into an alpha character before the number, it will return a 0.

Debug.Print VAL(" 123.4") = 123.4
Debug.Print VAL(" 123.4 pounds ") = 123.4
Debug.Print VAL("65.00") = 65
However:
Debug.Print VAL("Price £65.00") = 0

Therefore you want to fix things like "Price £65.00" first.

Debug.Print Val(Mid("Price 65.00", Instr("Price 65.00"," "))) = 65

Notice that I took out the £ sign. Val is suppose to ignore things like
currency symbols; however, it didn't even when I changed it to $ for my
USA-set computer. Therefore you might need to do a plain old find and replace
on the £ symbol or anything else after the first space in the string before
the number.

Also you want to make sure that there is a space before running the Mid
function. Create a field in the query like:
Spaces: Instr([TheFieldName]," ")))

And a criteria on that field of: >0