How to Return Only the Numbers in a Text Field (Plus the Decimal)

[doyle60]

I have a table that has a text field in it that contains a number.
The trouble is that the number is sometimes proceeded by a dollar sign
or some type of other currency sign and sometimes simply without any
such sign. Also, the zeros can be displayed as "-" (without
quotations) or "$-" or simply null.

Is there a way in a query to just return the numbers (plus the
decimal)? In other words, I need the following returned for these
values:

[Null] 0
$- 0
- 0
C- 0
$13.29 13.29
13.29 13.29
C13.29 13.29

Where "C" is any currency symbol from the British pound to the
Bulgarian whatever.

The above may be all the different types. I do not believe I will
ever see "$ 13.29", that is with blanks between the sign and the
numbers. But you never know.l So a universal function for only
returning the numbers and the decimal would be great. Thanks,

Matt

 

[doyle60]

Thanks for the quick reply. The line "GetNumber = Val(Mid(varText,
k)" appears red when I place it in a module. I tried to edit it
myself to fix it but no go.

1) I supposed that when I use this in a query, it could look like
this:

DSValueAdj: GetNumber([DSValue])

Where "DSValue" is the sloppy number and "DSValueAdj" would be the
cleaned up version.

2) Should I put the code in as a module or a class module?

Thanks,

Matt

 

[doyle60]

In closing down, I noticed that I really didn't save the module I
created or was updating. I'm new to Access 2007 and this seems a
little odd. Anyway, I put another peren at the end of the loine that
was red:

DSValueAdj: GetNumber([DSValue]) )

and it didn't turn red, seeming to accept it.

I then saved it properly and ran the query. But in every odd example
of sloppy numbers, the function returned

$0.00

1) I see that you have "As Currency" in your public function line. I
would rather it be just a number field, double.

Thanks so much,

Matt

 

[doyle60]

Thanks for the reply.

1) I need it as a double because I will use it for non-currency fields
as well.
2) How do I exactly change it to a double, just change the word
Currency to Double or Number?
3) I didn't mean to say that it formats it as $0.00, but that all the
"values" are 0.00. So if the cell is "-" it returns 0.00. If the
cell is $4.00, it returns $0.00. And if the cell is a true number
like 67.86, it returns $0.00. Every thing is a value of 0. I made a
few experiments but nothing worked.

Thanks again,

Matt

 

[doyle60]

Wonderful. It works. (I just realized that I didn't mention anything
about commas, as in, 1,433. But it doesn't seem that this will be an
issue in this project. If it is, you may see me post again.)

Thanks so much. This is a wonderful help.

Matt

 

[doyle60]

Ignore that comment about commas. What was I thinking? It doesn't
matter, of course.

Matt

 

[doyle60]

I was right the first time. The commas are a problem. I checked it
and somehow, oddly, most embarressingly, missed that it doesn't work
with commas. I was mockingly cocky with my wife when she was checking
the data. Then boom, she noticed this big descrepency.

This is what my text query looks like, showing what the code does:

TextNum TranslateIt
- 0
$- 0
L- 0
$5.22 5.22
$55.66 55.66
55.98 55.98
About 2.22 2.22
66.6pence 66.6
5.6q6 5.6
5,000 5
5000 5000

As you can see above, "5,000" returns just "5". Can you make it so
the code returns "5000"? Also, "1,000,000" would have to return
"1000000", etc. I don't mind if the code has "5.5gg6" return "5.56"
either.

I tried to edit the code by changing "[0-9.]" to "[0-9,.]". But this
didn't work.

Thanks so much and sorry to trouble you again. The reason why I
thought my comment was so stupid and told you to ignore it was for
some reason I thought the code skipped the non 1-9. characters and
continued on, instead of stopping so "1,000" would return "1000" and
formatting would make it "1,000" again.

Anyway, thanks again,

Matt

 

[doyle60]

I did a tempory fix with the replace function. It makes the query
pretty heavy but it fixed the comma issue. But now I have another
problem. I see that there are negative numbers. I didn't think there
were any.

They come in with perens and are translated with the code like this:

TextNum TranslateIt
$(5.14)-------5.14
(0.30)--------0.3
-3.0-----------3.0

So in other words, they are turned into positives. Do you think you
could fix this? Adding the "-" to the list seems to fix the last
case. (I could do another Replace function to turn the first peren
into a negative, I suppose.

Anyway, thanks for your time again,

Matt

 

[John Spencer]

Pardon me for jumping in.

You can modify the function as follows and it should handle most cases. It
did handle all the ones you have posted. The problem is that there is always
going to be something in string field that cannot be converted without very
complex coding. For instance "Pence (66)" will come back as 66 (no negative).

Public Function GetNumber(varText As Variant) As Double
Dim k As Integer

GetNumber = 0
If IsNumeric(varText) Then
'if vartext is recognized as numeric then
'CDbl can convert it handles $(23.42) easily
GetNumber = CDbl(varText)

ElseIf Not IsNull(varText) Then
For k = 1 To Len(varText)
If Mid(varText, k, 1) Like "[0-9.-]" Then
GetNumber = Val(Mid(varText, k))
Exit For
End If
Next k
End If
End Function


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[doyle60]

Thanks. This seems to work for everything I should realistically find
in the excel spreadsheets being transferred.
Thanks again, John, and thanks Marshall,

Matt