Formula to extract only numbers and decimal

  • Thread starter Thread starter lindag
  • Start date Start date
L

lindag

I have several fields that are populated inconsistantly.

Ex.

Final: $500.00
Pmt 500.00 lost?
$500.00


Is there a way to only extract the numbers and decimals? I have
written several different formulas to return data to the right of the
";" or "$" but am still having issues with fields that are like "Past
Due$500.00" & "$80.00 lost?"
 
lindag said:
I have several fields that are populated inconsistantly.

Ex.

Final: $500.00
Pmt 500.00 lost?
$500.00


Is there a way to only extract the numbers and decimals? I have
written several different formulas to return data to the right of the
";" or "$" but am still having issues with fields that are like "Past
Due$500.00" & "$80.00 lost?"
Click to expand...

Do they all contain decimals?
 
I have several fields that are populated inconsistantly.

Ex.

Final: $500.00
Pmt 500.00 lost?
$500.00


Is there a way to only extract the numbers and decimals? I have
written several different formulas to return data to the right of the
";" or "$" but am still having issues with fields that are like "Past
Due$500.00" & "$80.00 lost?"
Click to expand...

Well, it's tricky. What number do you want if the field contains "There were 1
or 2 cases where $400 was lost"?

That said here's some UNTESTED AIR CODE which might work:

Public Function GetMoney(strIn As String) As Currency
Dim iPos As Integer
For iPos = 1 to Len(strIn)
If IsNumeric(Mid(strIn, iPos, 1)) Then
GetMoney = Val(Mid(strIn, iPos))
Exit Function
End If
Next iPos
End Function

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
Back
Top