Paste special text leaves trailing spaces in Excel

Posted on

QUESTION :

I wrote a script that creates an HTML table like this for a coworker:

<table>
    <tr>
        <td>06/21/2014</td>
        <td>AAA</td>
    </tr>
    <tr>
        <td>06/22/2014</td>
        <td>BBB</td>        
    </tr>
    <tr>
        <td>06/23/2014</td>
        <td>CCC</td>        
    </tr>       
</table>

When she pastes it into Excel, she either gets this:

|6/21/2014|AAA|
|6/22/2014|BBB|
|6/23/2014|CCC|

Where the date strings have had the preceding 0 removed, this breaks a find/replace we’re trying to do on dates. Or, if she tries to paste special -> text on her windows PC she gets this:

|06/21/2014|AAA |
|06/22/2014|BBB |
|06/23/2014|CCC |

If I paste special on a mac, it works fine. Is there a way to:

  1. turn off default auto date formatting for all new files?
    OR
  2. make the paste special text stop adding trailing spaces?

ANSWER :

If you wish to tell Excel to NOT try automatic data type determination, then simply add a ' character before the data you enter into a cell.

When using paste, the ' might end up be visible though. A search and replace on ' to ' (from and to being the same) might help to remedy that (not tried).

Leave a Reply

Your email address will not be published.