Converting Text to Numeric
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. |
Converting Text to Numeric
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 |
Converting Text to Numeric
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 -----Original Message----- 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. . |
All times are GMT +1. The time now is 09:53 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com