Extracting words from a string up to a character count

Domm Rey

New member
Joined
Jun 6, 2013
Messages
2
Reaction score
0
Points
0
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

Microsoft Excel - Example - Truncating words up to character count.jpg
 

Attachments

  • Example - Truncating words up to character count.xlsx
    11.2 KB · Views: 15

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
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...
 

Domm Rey

New member
Joined
Jun 6, 2013
Messages
2
Reaction score
0
Points
0
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!
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
should the first part be?

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

JeffreyWeir

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
357
Reaction score
0
Points
0
Location
New Zealand
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:
Top