MS Office 365. imported columns will not resolve as nrs 1

[jlockley]

In every downloaded account spread sheet imported in utf8 (other formats also attempted) I am unable to get any numbers to calculate. Formulas return zero.
Attempted so far
Using regular interfaces to change format to numerical.
Special paste as values.
Special paste multiplying all column by 1 (both general and numerical format.
Clean (another issue..Excel has been importing the delimiters as non printing characters)
Trim. So no leading or trailing spaces.
Two of these accounts are from international banks. Three from US sources.
Other issues which may be clues: Opening CSV format spreadsheets imported directly from Bank of America resolves rows but not columns. Importing them using the standard import option does the same. Using legacy assigns columns but imports the text separator. A clipboard shot of the imported nrs:
"-81.83 "
"-3.90 "
"2363.28 "
(this was before the trim operation. I need to double check this.)
The column after cleanup.
2363.28
-47.38
Update. Using the standard import procedure for 365 rather than the legacy import on one csv file columns resolve and the numbers resolve in numerical format, without decimal points, summing to total expenditures of -1764865 last year. I see a workaround here, but something is definitely not working correctly. Possibility of the "." not being accepted?

-79.20
-8.67
-149.99
-39.29
-101.30
-55.53
-1.25
-21.69
-7.99
-28.20
-6.68
-38.06
-5.99
-35.02
-49.99
-256.65
-10.73
-10.30
-32.42
Further frustration/clues
attempting to add the entries separately using a calculator I see that they do not paste as they appear in Excel.
-79.20 pastes as -799
-2.82 pastes as 2.82
10.00 may paste as 1000 or 1.00
This is via Clipmate. They have been contacted as well.
Any insights will be appreciated.

 

[SkipVought]

Hi,

I’m not entirely sure exactly what you’re talking about. So I have some questions.

“Opening CSV format spreadsheets imported directly from Bank of America resolves rows but not columns.”

What does that mean: resolves rows but not columns?

I would NEVER open a .csv using Excel but rather IMPORT. I have had instances where the selected delimiter in the IMPORT dialog did not produce the desired parsing. So I’d open the .cvs in NOTEPAD and COPY/PASTE the delimiter into the Find/Replace dialog and replace with a KEYBOARD delimiter. Then IMPORT the modified .csv file into Excel.

You may need to do the same replace process with the DECIMAL and/or the MINUS sign character.

I would hate to think that the characters, 0 thru 9 might require replacement, if some extended character set were used in the remote accounting systems.

I’m guessing that nrs is your abbreviation for numbers. Yes?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[jlockley]

Hi Skip. Nice to hear from you again.
Thanks for responding.
I apologize. I was up until 3:00 dealing with this and am a bit punchy.

So to your questions.

I’m not entirely sure exactly what you’re talking about. So I have some questions.

“Opening CSV format spreadsheets imported directly from Bank of America resolves rows but not columns.”
If I open the file directly from the site (at least one of them but I think all) All of the data for each single transaction is shown in a single cell, ie. a single oclumn(
What does that mean: resolves rows but not columns?

I would NEVER open a .csv using Excel but rather IMPORT. I have had instances where the selected delimiter in the IMPORT dialog did not produce the desired parsing. So I’d open the .cvs in NOTEPAD and COPY/PASTE the delimiter into the Find/Replace dialog and replace with a KEYBOARD delimiter. Then IMPORT the modified .csv file into Excel.
I began by importing and have been doing so mostly. I agree, but I am trying everything. That is a great tip. I will.
You may need to do the same replace process with the DECIMAL and/or the MINUS sign character.
As I continue to work on this I am beginning to suspect that the decimal is the culprit, or that the file is being read with European numbers. I am just about to fiddle with the system separators. I think I mentioned that an imported text file lacked decimal points, leaving me with an apparent 1.7 million in annual spending on one card. (I don't remember buying that private jet. I wonder where I left it parked).Unfortunately Paste Special has tried to get finicky (Only offering the utf and text options) but I seem to be able to use a formula to multiply the cells by .01, so that's a workaround. Note that I spent the last ten months in Germany, but I reinstalled the program from the US a week ago.
I would hate to think that the characters, 0 thru 9 might require replacement, if some extended character set were used in the remote accounting systems.
I am beginning to suspect that Excel thinks its speaking German, but that can't be the only issue. So riddle me this: I avoided replacing the decimal with say an @ sign, but if I do and then replace it again, possibly with a comma (German, which I could then import, do you think that might work? I will get back to you. I cannot tell you how much I appreciate your help.

I’m guessing that nrs is your abbreviation for numbers. Yes?

 

[jlockley]

Another note: The most recent import was in US OEM format, which seems to be an improvement.

 

[SkipVought]

Can you COPY half a dozen records from the .csv file, CHANGE the client/bank names, SAVE and then upload?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[jlockley]

I tried before. I will do so again with a couple of them.

 

[jlockley]

Here´s a start. Imported as tab delimited. Have not done anything wit the text qualifiers yet (the quotation marks) I am simply going to delete them for the next step, as they don´t seem to function as they should. The columns parse correctly, though.

 

[jlockley]

Does not parse correctly.
I don't know if this will upload. It is on the desktop, and my webspace is down.
The file was uploaded as a tab delimited .dat file and renamed txt.
While the wildly inaccurate account totals (I wish) are numeric and can be manipulated, the amount totals, which appear to have populated without hidden characters, do not.
The numeric form with decimal point and comma have been corrected.

 

[jlockley]

Sorry..only the last entry is incorret..it was a trial sum.

If that does not function, here

Date Description Amount Running Bal.

02/09/2018 "Wire Transfer Fee" "-15.00" "15392.25"
04/24/2018 "CHASE MANHATTAN CREDIT CARDS Bill Payment" "-395.32" "14489.37"
05/21/2018 "Chase Manhattan Credit Cards Bill Payment" "-791.92" "13697.45"
05/23/2018 "American Express Bill Payment" "-35.00" "13662.45"
05/29/2018 "Chase Manhattan Credit Cards Bill Payment" "-388.34" "13274.11"
05/31/2018 "Bank of America Credit Card Bill Payment" "-298.00" "12976.11"
06/15/2018 "BANK OF AMERICA CREDIT CARD Bill Payment" "-298.13" "12677.98"
06/19/2018 "transfer business to core" "15663.08" "28341.06"
06/19/2018 "Counter Credit" "800.00" "29141.06"
06/19/2018 "Adjustment/Correction Of Posted Item" "-100.00" "29041.06"
06/19/2018 "American Express Bill Payment" "-475.63" "28565.43"
06/19/2018 "Chase Credit Cards Bill Payment" "-373.76" "28191.67"
06/29/2018 "Bank of America Credit Card Bill Payment" "-174.01" "28017.66"

 

[jlockley]

If I may, would you have any idea what is wrong with this function?
=TRIM(SUBSTITUTE(C4,CHAR(160),CHAR(32)))
Excel is rejecting all functions for the amount column.

 

[SkipVought]

What's wrong with this from the second upload...?

[pre]
Summary Amt.
13654.43
863.06
-15
[highlight #FCE94F]14502.49[/highlight]

Amount Running Bal. MyCheck
13654.43
863.06 14517.49 14517.49
-15 14502.49 [highlight #FCE94F]14502.49[/highlight]

[/pre]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[SkipVought]

can't use your 11 Feb 19 01:29 post as there is no valid delimiter. SPACE does not work here!

I'm not getting any problem with your Amount column???

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[jlockley]

The sample can now be manipulated
Removed initial spaces manually by copying to text. No trailing spaces were visible. Excel would not allow trim.
Removed non breaking spaces.
Trim
Clean
This is a service provided by a Bank to its customers. There must be a simpler way.

 

[SkipVought]

Was there a question in there?

Or was this a resolution statement of sorts?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[jlockley]

Or was this a resolution statement of sorts?
Pretty much. The second column would not add and did not show as numeric on COUNT.
I can't figure it out, since the bank said it resolved for them. I have checked all my settings and find nothing amiss.
Still, I think I have it after cleaning out non breaking spaces and non printing characters (they may already have been stripped in the upload you received), multiplying everything by 1.00, and cutting and pasting back with paste special.
That's one of five statements. I hope the same actions will work for the others.
Thank you very much.
I am glad the upload worked.