Converting Alphameric To Double?

[(PeteCresswell)]

I'm scraping a spreadsheet.

User selects the whole sheet, does a Ctl-C, then I parse the copy
buffer.

For most of the numeric columns, I just do a Cdbl(columncontents)
and it's good to go. e.g. Cdbl("30,000") = 30000 and
Cdbl("$5.32") = 5.32.

However one column contains something called "SEC Fees" and the
users have it formatted so that if there is no fee, I wind up
trying to convert "$-" - which causes Cdbl() to choke.

Val() doesn't choke, but it truncates anything after a comma.
e.g. Val("$3,123.45") = 3

I guess I could wrap it in a couple of Replace() calls to get rid
of the offending minus sign and dollar sign - or, more likely,
write a function to strip out all offending characters once I
figure out what they are.

But I'm wondering if I'm missing something obvious - like some
other canned function.

?

 

[Ken Snell \(MVP\)]

I don't know of another builtin function that would handle the "$-" for
CDbl. I'd go with the Replace function, or with one to remove all nonnumeric
characters:

' ** This function strips all nonnumeric characters from a text string.
' It retains decimal points and "minus" signs.


Function StripAllNonNumericNonDecimalPointNonHyphenChars( _
varOriginalString As Variant) As String
Dim blnStrip As Boolean
Dim intLoop As Integer
Dim lngLoop As Long
Dim strTemp As String, strChar As String
Dim strOriginalString As String
On Error Resume Next
strTemp = ""
strOriginalString = Nz(varOriginalString, "")
For lngLoop = Len(strOriginalString) To 1 Step -1
blnStrip = True
strChar = Mid(strOriginalString, lngLoop, 1)
For intLoop = Asc("0") To Asc("9")
If strChar = Chr(intLoop) Or strChar = "." Or _
strChar = "-" Then
blnStrip = False
Exit For
End If
Next intLoop
If blnStrip = False Then strTemp = strChar & strTemp
Next lngLoop
StripAllNonNumericNonDecimalPointNonHyphenChars = strTemp
Exit Function
End Function

 

[John Spencer]

You might try the IsNumeric function. It is pretty good on determining
whether or not a string can be converted to a number type

IIF(IsNumeric(ColumnContents,Cdbl(columncontents),Null)

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

 

[a a r o n . k e m p f]

can't you merely put a simpler format mask on the column 'SEC FEES'