asked on

Excel: "0.0000000000000000" stored as TEXT (not numeric zero)

I am importing a report into Excel (generated by SSRS).

Some fields contain: "0.0000000000000000" which causes Excel to store that value as text.

This scenario happens in hundreds of reports with many fields so changing the source isn't easy.

I was hoping to find a solution (maybe a macro) or technique which would enable to bulk convert that to a numeric 0 value.

I'm not loving some of the techniques (like multiplying by 1).

The cells are not contiguous and some of the worksheets are huge.

Any ideas?

-Mike

Madison Perkins

there should be a way to create a macro to clean up the spreadsheet. the devil will be in the details. sanitize the spreadsheet and attach to post.

Russell Fox

I don't know a way to fix it en masse, but you can try altering the textbox properties. Right-click that cell in the tablix, select "Text Box Properties...", click "Number", and set the decimal places you want to see, comma format, etc. I'm guessing that just changing it from "Default" to "Number" will fix the issue when it's exported into Excel. But, of course, you'll need to do that for every textbox in every report that's having this issue.

Subodh Tiwari (Neeraj)

You may try something like this...

Sub ConvertToNumbers()
Dim lr As Long
Application.ScreenUpdating = False
'Assuming the column A is the target column with those Numbers stored as Texts
lr = Cells(Rows.Count, "A").End(xlUp).Row
Range("A2:A" & lr).TextToColumns
Application.ScreenUpdating = True
End Sub

Open in new window

mike2401

ASKER

Thanks Neeraj.

I guess the issue is the range.

It could be A1 , B17, AC1:AD13 , etc.

I was hoping to have some kind of global macro my users could invoke to deal with all these 0.0000000000 's :-)

Subodh Tiwari (Neeraj)

In that case you may try something like this...

Sub ConvertToNumbers()
Dim Rng As Range
Dim c As Long
Application.ScreenUpdating = False
Set Rng = Selection
For c = 1 To Rng.Columns.Count
    Columns(Rng.Columns(c).Column).TextToColumns
Next c
Application.ScreenUpdating = True
End Sub

Open in new window

The above code will convert the Numbers stored as Text to real Numbers of the selected cell's column.
So if the user wants to apply the above code to the column A, the user should select one cell in column A and run the macro. And if the columns are AC1:AD13, user needs to select the any two adjacent cells in column AC:AD e.g. AC1:AD1 etc and the macro will work on columns AC:AD.
You may assign a shortcut key to the macro which users may press to run the macro.

ASKER CERTIFIED SOLUTION

Shums Faruk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mike2401

ASKER

Interesting idea. When I ran the Macro, it died on this line:
For Each MyRng In Ws.UsedRange.SpecialCells(xlCellTypeConstants)

with an error: Runtime Error 1004: No cells were found.

I did rename the tab to "Sheet1" so that shouldn't be it.

Any ideas?

Shums Faruk

Please upload your sample workbook. May be your cells are not Text Format.
Try below:

Sub ConvertTextToNumber()
Dim Ws As Worksheet
Dim MyRng As Range
Set Ws = ThisWorkbook.Worksheets("Sheet1") 'Change As Needed
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
For Each MyRng In Ws.UsedRange.Cells
    If Not IsEmpty(MyRng) Then
        MyRng.Value = CDec(MyRng.Value)
    End If
Next MyRng
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
End Sub

Open in new window

mike2401

ASKER

Thanks a bunch! I'm not sure what problem I had initially but it does work.

AWESOME! Thanks so much!

Shums Faruk

You're Welcome Mike! Glad I was able to help

Selim Kutlu

whenever I want to convert a text to a numeric value I always use in the report in the expression itself Cdec(variable).
it converts the variable to a numeric value that you can also use to make calculations etcetera

Excel: &quot;0.0000000000000000&quot; stored as TEXT (not numeric zero)

Excel: "0.0000000000000000" stored as TEXT (not numeric zero)