Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    163
    Thanks
    2
    Thanked 0 Times in 0 Posts

    remove blank spaces after cut and paste (Office 2000)

    Hi,

    I've had this problem before and wonder if anyone else has found a solution. I exported various tables from Business Objects to a HTML file. When I cut and paste the tables into Excel, there are blank spaces after text cells which impact of the formatting. I have the cells set to wrap text, but need to delete all the blank spaces to get the proper cell height and alignment.

    I can cut out the blank spaces after the text and it will format properly, but this involves hundreds of lines of data on dozens of spreadsheets. What is happening is text that should only fill one line is taking 2 or 3 lines of space most of which is blank.

    If anyone has run into this problem and found a solution I would really appreciate learning how to work around it.

    Thanks

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: remove blank spaces after cut and paste (Office 2000)

    Pick or insert an empty column. Lets say your strings with the spaces is in column A. In row 1 of your empty column enter the formula:

    <pre>=TRIM(A1)
    </pre>


    Copy that formula down the empty column as far as the strings go in column A. This should replace all multiple spaces with single spaces.

    Now select the column with the formula and copy it. Then select the original column and do a Paste Special values. Finally delete the column with the formula.

    If this text is comming from HTML, then there could also be sticky spaces in the text (ASCII 160). If so, you will need to use Find/Replace to replace all of those with normal spaces before doing the above.
    Legare Coleman

  3. #3
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: remove blank spaces after cut and paste (Offic

    I have some data that came from HTML and has some trailing spaces that persit after TRIM and paste special values so a search has led me to this thread.

    I tried Replacing ^s with a space (which is how you would replace non-breaking spaces in Word) but got a 'not found' message so:
    either, my Replace criterion was wrong - in which case, can any one put me right?
    or, my data doesn't have any sticky spaces - in which case, any suggestions?

    stuck

  4. #4
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: remove blank spaces after cut and paste (Offic

    There is a free add-in from Chip Pearson called Cell View

    It tells you the exact characters in a cell including their character codes.

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

    Re: remove blank spaces after cut and paste (Offic

    Excel does not use the ^something codes that Word utilizes for special searches. To search for the non-breaking space Chr(160), type Alt+0160 in the Find What box.

    If that isn't it, select one of the trailing spaces, copy it to the clipboard and paste it into the Find What box of the Replace dialog.

  6. #6
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: remove blank spaces after cut and paste (Offic

    <hr>select one of the trailing spaces, copy it to the clipboard and paste it into the Find What box<hr>

    <img src=/S/doh.gif border=0 alt=doh width=15 height=15> soooo easy <img src=/S/blush.gif border=0 alt=blush width=15 height=15>, why didn't I think of that? <img src=/S/drop.gif border=0 alt=drop width=23 height=23>

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> (un)stuck

Posting Permissions

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