CoolData blog

29 January 2010

Friday tip: Stop Excel from messing with long text strings

Filed under: Excel — Tags: , , — kevinmacdonell @ 12:23 pm

Ever try pasting a long string of text into Excel only to have the cell truncate at 255 characters? I hate this.

The problem crops up when I paste the results of a query of our database out of MS Access and into Excel. The queries I’m working with this week are “last and next action” reports for major gift prospects, and sometimes the Notes column contains more text than Excel likes. The result is that my notes are cut off after a certain number of characters. I was familiar with this problem when working with the constraints of Excel 2003, but I was dismayed to encounter the same problem in Excel 2007. I Googled around in desperation, to no avail.

The solution I found by accident is stupidly simple. Maybe I’m the only person who didn’t already know how to deal with this? Anyway, here it is:

Once you’ve copied the columns from your source (Access or whatever), open Excel and right-click on cell A1. Choose Paste Special, then choose CSV. Click OK to paste.

This step will prevent Excel from truncating long text strings. I don’t know why this works, and I don’t care. It just works.

Note: This is for Excel 2007. I haven’t tested it for Excel 2003, and I haven’t tested with other Paste Special options.

About these ads

4 Comments »

  1. great tip, no I didn’t know, but will try it next time I encounter this mildly troublesome feature of Excel. Thanks!!

    Comment by Diane — 29 January 2010 @ 5:19 pm

  2. Excellent tip, Kevin. I’ve been annoyed by the same thing for many years – Thank you for passing along a solution.

    p.s. Love your blog

    Comment by Carolyn Marshall — 1 February 2010 @ 10:27 pm

  3. That’s a lifesaver. Thanks.

    Comment by James Hollomon — 29 August 2012 @ 1:47 pm

  4. Thank you so much!
    This was driving me mental – I though I had a corrupt spreadsheet.

    Comment by Jonathan — 25 September 2012 @ 10:16 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Silver is the New Black Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 1,045 other followers

%d bloggers like this: