+ Reply to Thread
Results 1 to 3 of 3

How to truncate the content of a cell?

  1. #1
    Registered User
    Join Date

    How to truncate the content of a cell?

    Hi there,
    I need to have a cell take some text information from an other cell and truncate the text to the first 30 words and end it with three trailing dots.

    For example:

    TrueMoneygames takes all the excitement of playing backgammon and poker in the real world and brings it online. We offer the Internet's most authentic backgammon playing environment and your favorite poker games in one place. Join the action today and play online backgammon and poker for real money or just for fun.


    TrueMoneygames takes all the excitement of playing backgammon and poker in the real world and brings Ö

    Is there a formula to achieve this? I donít seem to find help in the excel help file.


  2. #2
    Forum Expert
    Join Date
    Michigan, USA
    MS-Off Ver
    I'm not sure of the exact formula to use, but it would be pretty easy with VBA. The only thing I see as an issue is you state you want the first 30 words, but your example only shows the first 16 words. Here is the code to return the first 30 words:
    Please Login or Register  to view this content.
    What you do is right-click on the sheet tab, select view code, and paste the above. The way this macro will work is you need to click on the cell that contains the long string. Upon running the macro, the first 30 words along with "..." will be returned in the cell directly below the original cell.

    If you want to return more or less than 30 words, just adjust the 30 in the above code. Also, you can have the revised code placed wherever you would like it on the sheet, by adjusting the "Offset" function above. (1, 0) means it will move it 1 row down and 0 columns to the right. So by adjusting these 2 numbers, you should be able to get it wherever you need it.

    The code can also be modified if you would like to select the cell to where you would like it posted, if you know the cell address of the long string. Post back if you need any slight revisions to the code.

    Last edited by jasoncw; 01-29-2007 at 12:56 AM.

  3. #3
    Forum Expert daddylonglegs's Avatar
    Join Date
    MS-Off Ver
    To do this with a formula.....here's a version of a formula I've seen posted in the Excel Newsgroups by Ron Rosenfeld, assuming your data is in A2

    =LEFT(TRIM(A2),FIND(CHAR(1),SUBSTITUTE(TRIM(A2)& REPT(" ",30)," ",CHAR(1),30))-1)&" ..."

    Note: if there are less than 30 words this will give the whole lot. If you want to get a different number of words just change the 30s in the formula for the number you require

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)


Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1