+ Reply to Thread
Results 1 to 6 of 6

Trim description into separate columns

  1. #1
    Registered User
    Join Date
    04-08-2013
    Location
    Hendersonville, TN
    MS-Off Ver
    Excel 2010
    Posts
    4

    Trim description into separate columns

    I have a description that I need to break out into separate columns based on a 35 character length, but I do not want to chop my words into pieces that do not make sense. Can anyone help with this? I have tried some trim/left options, but I am still having issues?

  2. #2
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2016
    Posts
    2,310

    Re: Trim description into separate columns

    Could you post some examples please....thanks
    Ernest

    Please consider adding a * if I helped

    Nothing drives me crazy - I'm always close enough to walk....

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: Trim description into separate columns

    Hi and welcome to the forum

    you will have to supply some sample data, as well as a few examples of your expected outcome. For instance, would you want the string split at a space before or after the 35 character limit?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    04-08-2013
    Location
    Hendersonville, TN
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Trim description into separate columns

    1/4-IN F X 1/4-IN F ADJUSTABLE NOZZLE HOLDER; STAINLESS STEEL
    FOOT VALVE; 1/4-IN F INLET X 1/4-IN F OUTLET; 7300PSI;21 GPM; STAINLESS STEEL

    These descriptions should break like the following: 1/4-IN F X 1/4-N F ADJUSTABLE NOZZLE HOLDER; STAINLESS STEEL
    FOOT VALVE; 1/4-IN F INLET X 1/4-IN F OUTLET; 7300PSI;21 GPM; STAINLESS STEEL

    I cannot get the original description to break into a legible word that makes sense. It cannot have over 35 characters total per column and the word or numbers have to not be broken in half. Please let me know if this helps.

    Thanks

  5. #5
    Registered User
    Join Date
    04-08-2013
    Location
    Hendersonville, TN
    MS-Off Ver
    Excel 2010
    Posts
    4

    Question Re: Trim description into separate columns

    I am using the following formulas to attempt to break these descriptions out:

    =TRIM(LEFT(LEFT(B279,35),FIND("^^",SUBSTITUTE(LEFT(B279,35)," ","^^",LEN(LEFT(B279,35))-LEN(SUBSTITUTE(LEFT(B279,35)," ",""))))))

    =LEFT(LEFT(TRIM(SUBSTITUTE(B279,C279,"")),35),FIND("^^",SUBSTITUTE(LEFT(TRIM(SUBSTITUTE(B279,C279,"")),35)," ","^^",LEN(LEFT(TRIM(SUBSTITUTE(B279,C279,"")),35))-LEN(SUBSTITUTE(LEFT(TRIM(SUBSTITUTE(B279,C279,"")),35)," ","")))))

    =TRIM(SUBSTITUTE(SUBSTITUTE(B279,C279,""),D279,""))

    When I try to use these formulas to break out my description(s) into separate columns it breaks them out like the following:
    1/2-IN M X 1/2-IN F SWIVEL COUPLING;STAINLESS STEEL (Original) (Breakout)- 1/2-IN M X 1/2-IN F SWIVEL COUPLING; STAINLESS STEEL

    This one should only break this into two columns, not three.

    Another example that is giving me a headache is this: TURBO NOZZLE; 3600PSI (03.0) (Original) (Breakout) TURBO NOZZLE; 3600PSI #VALUE
    This one should not have but one column with no breakout???????

    Let me know if these examples help.

  6. #6
    Registered User
    Join Date
    04-08-2013
    Location
    Hendersonville, TN
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Trim description into separate columns

    If the examples above are okay to show, please let me know. The formulas above are not quite right. If the description is under 35 characters long it should just basically copy the description over and not do a breakout.

+ 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