+ Reply to Thread
Results 1 to 3 of 3

How to truncate the content of a cell?

  1. #1
    Registered User
    Join Date
    01-28-2007
    Posts
    2

    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:

    ORIGINAL CELL:
    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.


    TRUNCATED CELL:

    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.

    Regards,
    Vincent

  2. #2
    Forum Expert
    Join Date
    12-29-2004
    Location
    Michigan, USA
    MS-Off Ver
    2013
    Posts
    2,208
    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.

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

  3. #3
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    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)

Bookmarks

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