Removing all text in a cell after a certain # of characters.

[tburg73]

I have this formula in cell AM2: =LEFT(TRIM(CONCATENATE($G2," ",$I2," ",$H2," ",$O2," ",IF(OR($P2>0,$Q2>0,$R2>0,$S2>0,$T2>0,$U2>0)," "&$AC2&" ",""),IF($N2<>"Regular",$N2,"")," ",$K2," ",IF(AND($J2<>"Polyester",$J2<>"Cotton Blend",$J2<>"Nylon Blend",$J2<>"Nylon"),$J2,"")," ",$L2," ",$AO2)),80)

It works great but, I want it to remove all the extra characters after the last space if there is over 80 characters so it wont leave any partial words

For instance if the cell contains something like: "This cell is way to long and has way too many charac" and the word character is truncated. I want it to just leave "This cell is way to long and has way too many"

Im sure its an easy fix, but I cant seem to get my head around it.

Thanks!

 

[VBA Geek]

does this do what you are asking?

If the cell has less than 81 characters then leave it as it is, if it has more than take the first 80 characters but without truncating the last word if the last word characters overlap 80 chars

Excel 2010
	A
1	It works great but, I want it to remove all the extra characters after the mike love me please
2
3	It works great but, I want it to remove all the extra characters after the mike
Sheet7

Cell Formulas
Range	Formula
A3	=IF(LEN(A1)<81,A1,LEFT(LEFT(A1,81),SEARCH(CHAR(11),SUBSTITUTE(LEFT(A1,81)," ",CHAR(11),LEN(LEFT(A1,81))-LEN(SUBSTITUTE(LEFT(A1,81)," ",""))))-1))

 

[tburg73]

Perfect! I was so close. I had a similar function going but I couldnt get it to work when I tried to replace the $A1 with the part of the original formula that pulled and combined the data. But this one worked! Thank you!

does this do what you are asking?

If the cell has less than 81 characters then leave it as it is, if it has more than take the first 80 characters but without truncating the last word if the last word characters overlap 80 chars


Excel 2010

	A
It works great but, I want it to remove all the extra characters after the mike love me please
It works great but, I want it to remove all the extra characters after the mike

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

</tbody>

Sheet7​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A3[/TH]
[TD="align: left"]=IF(LEN(A1)<81,A1,LEFT(LEFT(A1,81),SEARCH(CHAR(11),SUBSTITUTE(LEFT(A1,81)," ",CHAR(11),LEN(LEFT(A1,81))-LEN(SUBSTITUTE(LEFT(A1,81)," ",""))))-1))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]