Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting time from text to numeric | Excel Worksheet Functions | |||
Converting Numeric values to Text | Excel Worksheet Functions | |||
converting text to numeric data | Excel Worksheet Functions | |||
Converting variable text strings to numeric | Excel Discussion (Misc queries) | |||
Converting Text into a Numeric Value and Totalling | Excel Worksheet Functions |