Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Toronto, Canada, Ontario
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Inserting numbers from word (xPRO)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Inserting numbers from word (xPRO)

    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.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Toronto, Canada, Ontario
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting numbers from word (xPRO)

    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

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Inserting numbers from word (xPRO)

    The numbers have leading spaces, and so are treated by Excel as text. Find and Replace the spaces with nothing in that column.
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Inserting numbers from word (xPRO)

    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: <alt>0160 (hold alt key and type (no quotes) "0160" on the numeric keypad)
    replace with: {leave it blank}
    <replace all>
    They will automatically turn them into numbers after eliminating the sticky space

    Steve

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Inserting numbers from word (xPRO)

    John,

    It is NOT a normal space. It is a sticky-space (ascii 160). see my <post#=286271>post 286271</post#> to Michael

    Steve

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Inserting numbers from word (xPRO)

    I need new glasses, they are hard to tell apart. <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Inserting numbers from word (xPRO)

    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

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting numbers from word (xPRO)

    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))

  10. #10
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Toronto, Canada, Ontario
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting numbers from word (xPRO)

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

    Michael

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •