formatting problem after download from external application

[alisam58]

hello all
when downloading data to an excel sheet (office 2010) from am external application (SAP) I have some numeric values that I need to sum.
the problem is that excel does not register the numeric formatting, on many levels.
the number appears as 0.000,000 (dot is thousand separator and comma is decimal separator)
even when selecting the cells and changing the format from "general" to numeric, Excel does not register the change.
I must change the number completely to 0000.00 AND to format the cell to "numeric" for excel to register that it is a numeric value.
How can I default the cells to be numeric and register the value as a numeric value?
or at least perform these changes as mass changes?

 

[steve the fish]

Are numbers less than 1000 appearing as text or numbers? Theres a lot of difference between 100.000 and 100,000

 

[alisam58]

yes: 8 appears as 8,000 and 4341 appears as 4.341,000

 

[steve the fish]

You could use these:

For a single cell: =SUBSTITUTE(SUBSTITUTE(A1,".",""),",",".")+0
For adding a range: =SUM(IFERROR(SUBSTITUTE(SUBSTITUTE(A1:A100,".",""),",",".")+0,0)) entered CNTL-SHIFT-ENTER

 

[kvsrinivasamurthy]

Select the range
Use Find and Replace (Ctrl+F)
Find ,000
Replace ( Nothng is to be typed)
OK

Use Find and Replace (Ctrl+F)
Find .
Replace ( Nothng is to be typed)
OK

Click outside the range
Select the range
Next to the selected cell or range of cells, click the error button that appears.
On the menu, click Convert to Number.

 

[alisam58]

I have done the replace but this is a report that we regularly download and do not want to have to do the replace every time.
I will try the formula: can I create a macro with this formula?

 

[kvsrinivasamurthy]

Select the Range and Run Ths Macro
Code:

Sub ReformatData()
    
    Selection.Replace What:=",000", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=".", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Value = Selection.Value
    
End Sub

 

[steve the fish]

Are the numbers always whole numbers?