text to currency

[Tinkerer]

I have a table made from importing a XLS spreadsheet that has some crummy
data. For one, there is a currency field that has the amount entered such
as: "$24.2 million". I need to strip the 'million' where it exists and save
the return in proper format, such as 24,200,000. I can take or leave the $
and the 000,000 for that matter if I can just get the numeric isolated.

Suggestions??? There will be new data added to this table in the future and
I would rather have it correct going forward, so I can't just use form/report
formatting...I need the data updated. OH...there are some null fields, too.

Thanks in advance!

 

[Marshall Barton]

I have a table made from importing a XLS spreadsheet that has some crummy
data. For one, there is a currency field that has the amount entered such
as: "$24.2 million". I need to strip the 'million' where it exists and save
the return in proper format, such as 24,200,000. I can take or leave the $
and the 000,000 for that matter if I can just get the numeric isolated.

Suggestions??? There will be new data added to this table in the future and
I would rather have it correct going forward, so I can't just use form/report
formatting...I need the data updated. OH...there are some null fields, too.

This could be a real messy operation if your data has a lot
of sloppy syntax. In some extreme scenarios, this can be
near impossible.

Here's an off the top of my head starter idea using a custom
function:

Public Function Fixit(funkynum)
Dim x As String
If Left(x,1) = "$" Then
x = Mid(funkynum, 2)
Else
x = funkynum
End If
x = Replace(x, "million", " * 1000000)
Fixit = Eval(x)
End Function

Easy enough to extend that to deal with thousand as well as
million, but if there could be personal preference
abbreviations, then it can quicky get out of control. Maybe
a separate table with the million, etc would help keep the
code under control and make it easy to add new, inventive
ways of spelling million, etc.