Excel Does Not Calculate Correctly Imported Data

Question

Excel Does Not Calculate Correctly Imported Data

Anonymous

Hi,

I'd like to analyze the statements I receive from my bank with Excel 2013. They are delivered in a *.csv format. After several hours of trying I finally managed to seperate the data into appropriate columns in Excel by using 'get external data'. For each transaction there is a line and each item of data tied to the transaction has its own column, e.g. text, amount, balance. Assigning a '-' to a debited amount is still an issue but not part of this question.

To calculate my total cashflow from last month I wanted to simply add all numbers in a column. However, the result only shows 0.00 although there is data in the fields. It is also impossible that the result is a coincidence because deleting a few lines also gives the same result. The deposit/debit column is formatted correctly with the currency format.

There is something that is strange. Anywhere in the spreadsheet where I enter a few numbers below each other Autosum finds these cells and calculates them correctly. In the deposit/debit column and the other imported columns it does not work. I have to do it manually.

How can I get Excel to add all the numbers in my columns?

Thanks in advance.

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

6 answers

Answer 1

Anonymous

Format an empty cell as General.

Select and copy that cell then select all other cells and Paste Special(in place)>Add>OK>Esc

Try to calculate those cells now.

Gord

0 comments

Answer 2

I had the same problem with Autosum showing the 0.00 result.

My issue with my imported numbers was that all of the numbers in my column had a leading space so they were being recognized as text no matter what formatting I applied to the cells.

Once the spaces were removed, issue solved.

Answer 3

Simon wrote:

Here is the file which contains the numbers which didn't add up. http://sdrv.ms/15uZBGN

[....]

The advice to use this formula: =ISTEXT(A1) was most helpful. It showed 'false' for a cell that I formatted repeatedly as General

[....]

So I looked up what my localization was and it was not set to US English. Having changed the settings I imported the data again and now it works.

[....]

I consider the problem as solved but if you think it can be done better without changing the localization I would also be grateful.

I think the localization settings are key to the problem. They impact how Excel interprets input; in particular, how it recognizes certain numbers (dates, currency, etc).

So I think the localization settings must match the "localization" of the data you import from the CSV file.

FYI, looking at the uploaded file (thanks), ISTEXT() returns TRUE for sheet1!F2:F5 and sheet2!F3:F5.

It does not matter how many times you change or reassert the numeric format (General or otherwise). Changing the format alone does not cause Excel to reinterpret the cell content.

I suspect the reason why reformatting "repeatedly" might have improved the situation is that you also effectively "re-entered" the data in the cells after you reformatted. (Although reformatting was not necessary.) For example, you might have selected the cell, then pressed Enter unconsciously.

In fact, you could use Text To Column to fix all the errant cells in a columnar range en masse, one column at a time.

I had thought to mention that previously. But I did not think that would be useful because that is effectively the same feature you use when you do Get External Data.

However, that certainly would be useful to do when you correct the localization after you imported the data.

Perhaps the following macro will help with that task. Remember that it should be executed after you correct any localization problem.

Option Explicit

Sub fixit()

Dim ws As Worksheet

Dim cel As Range

With Application

.ScreenUpdating = False

.Calculation = xlCalculationManual

.EnableEvents = False

End With

For Each ws In Sheets

ws.Activate

For Each cel In ws.UsedRange.SpecialCells(xlCellTypeConstants)

If cel.NumberFormat <> "@" Then

If WorksheetFunction.IsText(cel) Then

cel.Value = cel.Value

End If

Next cel

Next ws

With Application

.EnableEvents = True

.Calculation = xlCalculationAutomatic

.ScreenUpdating = True

End With

MsgBox "done"

End Sub

Answer 4

Hi,

Here is the file which contains the numbers which didn't add up. http://sdrv.ms/15uZBGN

I found a solution thanks to both of you.

The advice to use this formula: =ISTEXT(A1) was most helpful. It showed 'false' for a cell that I formatted repeatedly as General and even added text to the numbers in the cells. This got me thinking. Although I formatted the columns 'Debit Amount' 'Credit Amount' hundreds of times correctly (as currency and general) I received weired outputs when I used 'Get External Data - From Text' to import the csv file.

Sometimes I got seemingly arbitrary long numbers and sometimes a German date format. Excel wouldn't accept that it was dealing with a currency here. So I looked up what my localization was and it was not set to US English. Having changed the settings I imported the data again and now it works.

However, I need to change my localization from time to time so this is only a workaround.

I consider the problem as solved but if you think it can be done better without changing the localization I would also be grateful.

Many thanks for your help.

Simon

Answer 5

Simon wrote:

I'd like to analyze the statements I receive from my bank with Excel 2013. They are delivered in a *.csv format. After several hours of trying I finally managed to seperate the data into appropriate columns in Excel by using 'get external data'.

[....]

The deposit/debit column is formatted correctly with the currency format.

[....]

In the deposit/debit column and the other imported columns it does not work. I have to do it manually. How can I get Excel to add all the numbers in my columns?

Sounds like the debit column et al data was treated as text when it was imported. That means they were not in a form that Excel recognizes as a number. Without concrete examples (always the best way to get an answer quickly), I can only guess.

To start, confirm my inference by verifying that =ISTEXT(A1) returns TRUE, where A1 represents one of the cells that is not summing. Then you might try putting the following in a parallel column:

=TRIM(SUBSTITUTE(SUBSTITUTE(A1,CHAR(160),""),CHAR(9),""))

That removes all possible spaces, tabs and non-breaking spaces (HTML &nbsp).

[EDIT] Actually, the following might be better because as it removes any interstitial spaces as well:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,CHAR(160),""),CHAR(9),""),CHAR(32),"")

Also, note that "" is two double-quotes side-by-side; no space between them.

Does SUM work with the parallel column? If yes, copy-and-paste-special-value the parallel column into the original column (A). Repeat the process for the remaining columns that do not sum.

If that does not work or if you are still confused or prefer a different solution, post the URL of an example Excel file (with just the errant columns of numbers) that you uploaded to a file-sharing website. The following is a list of some free file-sharing websites; or use your own.

Box.Net: http://www.box.net/files

Windows Live Skydrive: http://skydrive.live.com

MediaFire: http://www.mediafire.com

FileFactory: http://www.filefactory.com

FileSavr: http://www.filesavr.com

RapidShare: http://www.rapidshare.com

If you choose to use Skydrive, do the following to get the public URL: right-click on the uploaded file icon, click on Sharing, Get A Link, and Make Public. Copy the URL and paste it into a response here. Then click Done in the Skydrive Sharing dialog box.

Share via

Excel Does Not Calculate Correctly Imported Data

6 answers