Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Numbers stored as text
I am having a problem with numbers being stored as text in Excel. I am
exporting the data from a SQL Server dB using a DTS package. I have tried converting the fields before export in DTS and this does seem to work temporarily, but the Excel file will eventually convert to all text. I have read the help regarding converting these fields from "numbers stored as text" to numbers, and have forwarded that information on to the end users. However, this can be only a temporary solution, as the end users do not want to do this every day. Thanks in advance for any help! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Numbers stored as text
If the data is contiguous you can modify the following VBA code to convert
the string values to numeric values. This way your staff can just run a macro following the retrieval of the data, which might make it easier for them. Sub TextToVal() Dim wb As Workbook Dim ws As Worksheet Dim strVal As String Dim lngVal As Long Dim lCounter As Long Dim r As Range Set wb = ActiveWorkbook Set ws = wb.Worksheets("Sheet1") ws.Activate Range("A1").Select Selection.CurrentRegion.Select Selection.Name = "MyData" Set r = Range("MyData") For lCounter = 1 To r.Cells.Count strVal = CStr(r.Cells(lCounter).Value) lngVal = CLng(strVal) r.Cells(lCounter).Value = lngVal Next lCounter Set wb = Nothing Set ws = Nothing Set r = Nothing End Sub -- Kevin Backmann "Thorkil" wrote: I am having a problem with numbers being stored as text in Excel. I am exporting the data from a SQL Server dB using a DTS package. I have tried converting the fields before export in DTS and this does seem to work temporarily, but the Excel file will eventually convert to all text. I have read the help regarding converting these fields from "numbers stored as text" to numbers, and have forwarded that information on to the end users. However, this can be only a temporary solution, as the end users do not want to do this every day. Thanks in advance for any help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
converting text to negative numbers! | Excel Worksheet Functions | |||
converting numbers to text | New Users to Excel | |||
How do I convert numbers stored as text with spaces to numbers | Excel Discussion (Misc queries) | |||
How to reformat numbers stored as text (apostrophe at beginning) | Excel Discussion (Misc queries) | |||
Converting Numbers to Text properly | Excel Discussion (Misc queries) |