Inserting numbers from word (xPRO)

Topic

New Reply

Greetings,
I copy a Visa statement from my bank’s internet service. I put it into Word where it is treated, very nicely, as a table. I muck it about a bit, then want to move it to Excel. When i do, the number are not treated as numbers, so every formula comes up 0. Excel also does not seemto like the pasting if it is direct.

Is there a way to do this? BTW, I can use a formula in the Word table, so Word recognizes them as numbers.

TIA,

Michael

Reply | Quote

Replies

What goes wrong if you try to paste directly into Excel?

There are several ways of forcing Excel to treat numbers as numbers:
– If you have a column of numbers being treated as text, select it, then choose Data | Text to Columns, make sure the Delimited option is chosen and click Finish without performing the intermediate steps.
– Select an empty cell and copy it to the clipboard; then select the problem cells and choose Edit | Paste Special…, click the Add option, then OK.
– It is also possible to create a VBA macro, but that may be overkill here.

Reply | Quote

Thank you, but neither method worked. The issue seems to be the numbers coming from the web. I copied them again and pasted directly into Excel, and the same thing happened. When I creat a formula, e.g., sum a column, I get a 0. Also, for instance, when I tell excel to treat the numbersas currentcy, it does not add $ signs.

I have attached a sample. I took it from the bank’s web site, saved it in Excel. (I wanted to do it directly from the web, but one can’t post an htm file.)

Thanks again,
Michael

Reply | Quote

The numbers have leading spaces, and so are treated by Excel as text. Find and Replace the spaces with nothing in that column.

Reply | Quote

John,

It is NOT a normal space. It is a sticky-space (ascii 160). see my post 286271 to Michael

Steve

Reply | Quote

I need new glasses, they are hard to tell apart.

Reply | Quote

I have seen it pop up peridically when you import. TRIM does not work and replacing the space with nothing does NOT find anything. I have seen many people frustrated on excel boards with it. It is one of the first things I look for in imported text that “acts” weird and refuses to behave.

Steve

Reply | Quote

John is close. The program made sure you would not lose the space so it used the “sticky-space” Ascii 160.
Select the range in D
Edit – replace
find what: 0160 (hold alt key and type (no quotes) “0160” on the numeric keypad)
replace with: {leave it blank}

They will automatically turn them into numbers after eliminating the sticky space

Steve

Reply | Quote

The formula =VALUE(RIGHT(D2,LEN(D2)-1)) also converts the abomination your bank gives you into a number.

FWIW I find that financial institutions give their downloaded statements in such horrible formats that its worth making a template with the figures the way I want then in say cols A-E , and a coloured area in cols G-K onto which I paste the figures as downloaded.
Eg. If G2 has the date and H2 has the amount with a sticky leading blank, A2 would be formatted with the date the way I want, and contain the formula =G2; and B2 would be formatted as currency and have the formula =VALUE(RIGHT(H2,LEN(H2)-1))

Reply | Quote

Thanks to all. Replacing the sticky space worked, and now I have a macro to do it.

Michael

Reply | Quote

Thanks to all. Replacing the sticky space worked, and now I have a macro to do it.

Michael

Reply | Quote