+ Reply to Thread
Results 1 to 8 of 8

Truncate Mulitple Words in a Cell

  1. #1
    Registered User
    Join Date
    04-04-2013
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2007, Excel 2010, Excel 2013, Office 365
    Posts
    21

    Truncate Mulitple Words in a Cell

    I have a text in multiple cells with varying lengths and number of words or numbers. I need to truncate each word, say to the first three characters, so the total lenght is no more than 25 characters. I am not sure how the TRIM, TRUN, LEFT, RIGHT or other defined functions might apply. A UDF might be best.

    Examples
    CONSTRUCTION SEQUENCING AND PHASING COMPOSITE PLAN
    CON SEQ AND PHA COM PLA


    ALIGNMENT HORIZONTAL CONTROL COMPOSITE PLAN
    ALI HOR CON COM PLA


    TEMPORARY ROADWAY NO. 4 ALIGNMENT DATA TABLES
    TEM ROA NO. 4 ALI DAT TAB

  2. #2
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Truncate Mulitple Words in a Cell

    Hi,

    Use this UDF :

    Please Login or Register  to view this content.
    Usage example :
    Please Login or Register  to view this content.
    1. I care dog
    2. I am a loop maniac
    3. Forum rules link : Click here
    3.33. Don't forget to mark the thread as solved, this is important

  3. #3
    Registered User
    Join Date
    04-04-2013
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2007, Excel 2010, Excel 2013, Office 365
    Posts
    21

    Re: Truncate Mulitple Words in a Cell

    Gives me a #NAME? error.

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Truncate Mulitple Words in a Cell

    Maybe using formula

    First, in cell C1 enter formula and pull it to the right until you see blank cell.

    =LEFT(TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",255)),(COLUMNS($A:A)-1)*255+1,255)),3)&" "

    then in B1

    =TRIM(CONCATENATE(C1,D1,E1,F1,G1,H1))

    Row\Col
    A
    B
    C
    D
    E
    F
    G
    H
    1
    CONSTRUCTION SEQUENCING AND PHASING COMPOSITE PLAN CON AND PHA COM PLA CON AND PHA COM PLA
    2
    ALIGNMENT HORIZONTAL CONTROL COMPOSITE PLAN ALI CON COM PLA ALI CON COM PLA
    3
    TEMPORARY ROADWAY NO. 4 ALIGNMENT DATA TABLES TEM NO. 4 ALI DAT TAB TEM NO. 4 ALI DAT TAB
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  5. #5
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Truncate Mulitple Words in a Cell

    Quote Originally Posted by UncleKevy View Post
    Gives me a #NAME? error.
    Look at the attached file.

    Put the code in module level. If you don't know how to do this, follow instructions from here :
    http://www.wikihow.com/Create-a-User...icrosoft-Excel
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    04-04-2013
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2007, Excel 2010, Excel 2013, Office 365
    Posts
    21

    Re: Truncate Mulitple Words in a Cell

    The UDF approach works. I must have messed up the insertion of the module originally. However, now that I think about it further, I would prefer to not use a function since in 2013 it needs to be an .xlsm extension and it needs to be distributed to a number of people. The COCATENATE approach also works, but I was hoping for a solution that was self contained in the cell. I will see if others come up with other ideas before closing this out. Thanks.

  7. #7
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Truncate Mulitple Words in a Cell

    You can have it in one formula but it going to be big.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    04-04-2013
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2007, Excel 2010, Excel 2013, Office 365
    Posts
    21

    Re: Truncate Mulitple Words in a Cell

    Thanks. I will play with this a bit and you are very correct in the length.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Truncate text in a cell after so many words or characters
    By theblade24 in forum Excel General
    Replies: 9
    Last Post: 06-07-2013, 01:29 PM
  2. sumifs with mulitple columns and mulitple criteria in each column
    By bkaufman in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 07-18-2012, 05:11 PM
  3. How to truncate the content of a cell?
    By vvvv in forum Excel General
    Replies: 2
    Last Post: 01-29-2007, 05:22 AM
  4. [SOLVED] Truncate number in cell
    By Chris Hedlund in forum Excel General
    Replies: 5
    Last Post: 05-12-2006, 11:30 AM
  5. Truncate words
    By jonr[email protected] in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-12-2006, 06:10 PM

Tags for this Thread

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