Results 1 to 5 of 5

Thread: Extracting words from a string up to a character count

  1. #1

    Extracting words from a string up to a character count



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Not sure if this can be done but here's hoping.
    At work we have descriptions of items that are in one row that are different character lengths per cell. We manually extract the words to fit into cells in other columns not to exceed a given character length (e.g. 15 characters). My question is, can one use a formula or macro that would break the description into other cells based upon character length, without breaking the words?
    I hope it is clear what I am looking for. Please see screenshot below showing what we manually do. I have also attached an example document.
    Thanks

    Click image for larger version. 

Name:	Microsoft Excel - Example - Truncating words up to character count.jpg 
Views:	14 
Size:	18.0 KB 
ID:	1373
    Attached Files Attached Files

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,499
    Articles
    0
    Excel Version
    Excel 2016
    Try these formulas...

    In B2:

    =IFERROR(TRIM(LEFT(LEFT(A2,15),FIND("^^",SUBSTITUTE(LEFT(A2,15)," ","^^",LEN(LEFT(A2,15))-LEN(SUBSTITUTE(LEFT(A2,15)," ","")))))),A2)

    in C2:

    =IF(B2="","",IFERROR(TRIM(LEFT(LEFT(TRIM(SUBSTITUTE(A2,B2,"")),15),FIND("^^",SUBSTITUTE(LEFT(TRIM(SUBSTITUTE(A2,B2,"")),15)," ","^^",LEN(LEFT(SUBSTITUTE(A2,B2,""),15))-LEN(SUBSTITUTE(LEFT(TRIM(SUBSTITUTE(A2,B2,"")),15)," ","")))))),TRIM(SUBSTITUTE(A2,B2,""))))

    In D2:

    =IF(C2="","",IFERROR(TRIM(LEFT(LEFT(TRIM(SUBSTITUTE(A2,B2&" "&C2,"")),15),FIND("^^",SUBSTITUTE(LEFT(TRIM(SUBSTITUTE(A2,B2&" "&C2,"")),15)," ","^^",LEN(LEFT(SUBSTITUTE(A2,B2&" "&C2,""),15))-LEN(SUBSTITUTE(LEFT(TRIM(SUBSTITUTE(A2,B2&" "&C2,"")),15)," ","")))))),TRIM(SUBSTITUTE(A2,B2&" "&C2,""))))

    copy these down.

    If you will need more than 3 15 char lines, copy the D2 formula to E2 change the initial C2 to D2, and change all the B2&" "&C2 to B2&" "&C2&" "&D2

    continue the pattern for any other columns added...


  3. #3
    NBVC,
    Thank you so much! It works beautifully. I can't even count how many hours I have spent breaking that all out.
    I went ahead and added that fourth line just in case there were any descriptions going over the three lines. However, I have found that whenever the third line isn't being populated, the fourth line comes back with the first lines info. Here is what I have in the fourth cell over (we actually have our descriptions starting in C2 so I adjusted the formulas to that):

    =IF(E2="","",IFERROR(TRIM(LEFT(LEFT(TRIM(SUBSTITUTE(C2,D2&" "&E2&" "&F2,"")),15),FIND("^^",SUBSTITUTE(LEFT(TRIM(SUBSTITUTE(C2,D2&" "&E2&" "&F2,"")),15)," ","^^",LEN(LEFT(SUBSTITUTE(C2,D2&" "&E2&" "&F2,""),15))-LEN(SUBSTITUTE(LEFT(TRIM(SUBSTITUTE(C2,D2&" "&E2&" "&F2,"")),15)," ","")))))),TRIM(SUBSTITUTE(C2,D2&" "&E2&" "&F2,""))))

    So for instance, if Kosher Dill Relish is in C2, D2 shows Kosher Dill, E2 shows Relish, F2 shows nothing, and G2 shows Kosher Dill.
    Can you see what is causing this to repeat?

    Thanks again so much!

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,499
    Articles
    0
    Excel Version
    Excel 2016
    should the first part be?

    =If(F2="","",....)

  5. #5
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    NBVC - unfortunately that fails if the 15th character in each slice just happens to be followed by a blank e.g. try your method on "The quick brown fox jumped over the lazy dog" and you will see that it returns "The quick" rather than "The quick brown".
    To get around this, you'll have to check if the 16th character is a blank.
    So that first formula (with my additions in bold) would be:
    =IFERROR(IF(MID($A6,16,1) = " ",LEFT($A6,15),TRIM(LEFT(LEFT(A6,15),FIND("^^",SUBSTITUTE(LEFT(A6,15)," ","^^",LEN(LEFT(A6,15))-LEN(SUBSTITUTE(LEFT(A6,15)," ",""))))))),A6)
    ...and you'd need to make some changes to the other formulas too (which I haven't done).

    I came up with a slightly different method with an array formula (which needs to be array entered by pushing Ctrl + Shift + Enter at the same time).
    This goes in A2:
    =IF(LEN($A2)<16,$A2,LEFT($A2,FIND("|",SUBSTITUTE($A2," ","|",MATCH(15,FIND("|",SUBSTITUTE($A2& " "," ","|",ROW($A$1:INDEX($A:$A,LEN($A2)-LEN(SUBSTITUTE($A2," ",""))+1))))-1,1)))-1))

    This goes in the next column over, and can be dragged without modification across further columns as needed:
    =MID($A2,SUM(LEN($B2:B2)+1)+1,FIND("|",SUBSTITUTE(MID($A2,SUM(LEN($B2:B2)+1)+1,LEN($A2))&" "," ","|",MATCH(15,FIND("|",SUBSTITUTE(MID($A2,SUM(LEN($B2:B2)+1)+1,LEN($A2))& " "," ","|",ROW($A$1:INDEX($A:$A,LEN(MID($A2,SUM(LEN($B2:B2)+1)+1,LEN($A2)))-LEN(SUBSTITUTE(MID($A2,SUM(LEN($B2:B2)+1)+1,LEN($A2))," ",""))+1))))-1,1)))-1)

    Both need to be array entered.
    Last edited by JeffreyWeir; 2013-06-07 at 02:14 AM.

Posting Permissions

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