Converting Text to Numeric

[Matt]

I have a spreadsheet that is drawn from a real-time
databse. The cells I need to work with are text formatted
with numbers having commas and decimals (e.g. 1,000.00).
Excel has a function called VALUE()which can convert a
text string to numeric, but it is not available in VBA.
VBA does have a similar function called Val(), but it
won't work if the text string has commas.

Can anyone suggest a VBA function or subroutine that can
convert a text string having numbers with commas to
numeric? Thanks.

 

[Guest]

Mat

Try using the substitute function

Val(WorksheetFunction.Substitute("1,000.5", ",", "")

Ton

----- Matt wrote: ----

I have a spreadsheet that is drawn from a real-time
databse. The cells I need to work with are text formatted
with numbers having commas and decimals (e.g. 1,000.00).
Excel has a function called VALUE()which can convert a
text string to numeric, but it is not available in VBA.
VBA does have a similar function called Val(), but it
won't work if the text string has commas

Can anyone suggest a VBA function or subroutine that can
convert a text string having numbers with commas to
numeric? Thanks

 

[Peter Atherton]

Mat

This should do it


Sub txt2Num()
Dim c
For Each c In Selection
If IsNumeric(c) Then
c.Value = c * 1
c.NumberFormat = "0,000.00" 'Alter foremat to suit
Else
c = c
End If
Next c
End Sub

Regards
Peter