Hi All

I need to trim the contents of a cell to 25 characters. It doesn't
matter if some text gets chopped from the end of the content ,
providing it's no longer than 25 characters.

Can someone kindly suggest a formula for this , please?


Thanks

Assuming you want to keep whats on the left:
=LEFT(A2,25)

alternatively:
=RIGHT(A2,25)

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Colin Hayes" wrote:


Hi All

I need to trim the contents of a cell to 25 characters. It doesn't
matter if some text gets chopped from the end of the content ,
providing it's no longer than 25 characters.

Can someone kindly suggest a formula for this , please?


Thanks

Colin Hayes wrote:

Hi All

I need to trim the contents of a cell to 25 characters. It doesn't
matter if some text gets chopped from the end of the content ,
providing it's no longer than 25 characters.

Can someone kindly suggest a formula for this , please?


Thanks


If you care where it gets chopped:

=IF(A1="","",IF(LEN(A1)<=25,A1,
TRIM(LEFT(SUBSTITUTE(LEFT(A1,25+1)," ",REPT(" ",99)),
LEN(SUBSTITUTE(LEFT(A1,25+1)," ",REPT(" ",99)))-99))&"…"))

Change the three 25's for a different length.

Glenn wrote:
Colin Hayes wrote:

Hi All

I need to trim the contents of a cell to 25 characters. It doesn't
matter if some text gets chopped from the end of the content ,
providing it's no longer than 25 characters.

Can someone kindly suggest a formula for this , please?


Thanks


If you care where it gets chopped:

=IF(A1="","",IF(LEN(A1)<=25,A1,
TRIM(LEFT(SUBSTITUTE(LEFT(A1,25+1)," ",REPT(" ",99)),
LEN(SUBSTITUTE(LEFT(A1,25+1)," ",REPT(" ",99)))-99))&"…"))

Change the three 25's for a different length.


A little better:

=IF(A1="","",IF(LEN(A1)<=25,A1,
TRIM(LEFT(SUBSTITUTE(A1," ",REPT(" ",99),
25-LEN(SUBSTITUTE(LEFT(A1,25+1)," ",""))+1),25))&"…"))

Glenn wrote:
Glenn wrote:
Colin Hayes wrote:

Hi All

I need to trim the contents of a cell to 25 characters. It doesn't
matter if some text gets chopped from the end of the content ,
providing it's no longer than 25 characters.

Can someone kindly suggest a formula for this , please?


Thanks


If you care where it gets chopped:

=IF(A1="","",IF(LEN(A1)<=25,A1,
TRIM(LEFT(SUBSTITUTE(LEFT(A1,25+1)," ",REPT(" ",99)),
LEN(SUBSTITUTE(LEFT(A1,25+1)," ",REPT(" ",99)))-99))&"…"))

Change the three 25's for a different length.


A little better:

=IF(A1="","",IF(LEN(A1)<=25,A1,
TRIM(LEFT(SUBSTITUTE(A1," ",REPT(" ",99),
25-LEN(SUBSTITUTE(LEFT(A1,25+1)," ",""))+1),25))&"…"))


And now there are four 25's to change.

You say you want to "trim the contents of a cell"... does that mean you want
your users to enter something into a cell and then you want to automatically
have Excel truncate the entry down to 25 characters within the SAME cell
that the entry was made in? If this is what you meant, then you can't do it
with a formula (the user's entry would replace any formulas with such
cells)... it will take a macro to accomplish this... but you will need to
provide some more information. What cell or range of cells should this
functionality be restricted to? Will any of the cells in this range have
formulas in them? Will any of the cells in the range have numerical data in
them?

--
Rick (MVP - Excel)


"Colin Hayes" wrote in message
...

Hi All

I need to trim the contents of a cell to 25 characters. It doesn't matter
if some text gets chopped from the end of the content , providing it's no
longer than 25 characters.

Can someone kindly suggest a formula for this , please?


Thanks

I need to trim the contents of a cell to 25 characters. It doesn't
matter if some text gets chopped from the end of the content ,
providing it's no longer than 25 characters.

Can someone kindly suggest a formula for this , please?

If you care where it gets chopped:

=IF(A1="","",IF(LEN(A1)<=25,A1,
TRIM(LEFT(SUBSTITUTE(LEFT(A1,25+1)," ",REPT(" ",99)),
LEN(SUBSTITUTE(LEFT(A1,25+1)," ",REPT(" ",99)))-99))&"…"))

Change the three 25's for a different length.

A little better:

=IF(A1="","",IF(LEN(A1)<=25,A1,
TRIM(LEFT(SUBSTITUTE(A1," ",REPT(" ",99),
25-LEN(SUBSTITUTE(LEFT(A1,25+1)," ",""))+1),25))&"…"))

This slightly shorter formula (using two less function calls) appears to do
what your formula is doing...

=SUBSTITUTE(LEFT(A1,25)," "&TRIM(RIGHT(SUBSTITUTE
(TRIM(LEFT(A1,25))," ",REPT(" ",99)),99)),"")&"…"

I would note that just adding the ellipses to the end can produce a
resulting string that is longer than 25 character. Our formulas also differ
on how they handle text whose 25th character is a space character.

--
Rick (MVP - Excel)

Rick Rothstein wrote:
I need to trim the contents of a cell to 25 characters. It doesn't
matter if some text gets chopped from the end of the content ,
providing it's no longer than 25 characters.

Can someone kindly suggest a formula for this , please?

If you care where it gets chopped:

=IF(A1="","",IF(LEN(A1)<=25,A1,
TRIM(LEFT(SUBSTITUTE(LEFT(A1,25+1)," ",REPT(" ",99)),
LEN(SUBSTITUTE(LEFT(A1,25+1)," ",REPT(" ",99)))-99))&"…"))

Change the three 25's for a different length.

A little better:

=IF(A1="","",IF(LEN(A1)<=25,A1,
TRIM(LEFT(SUBSTITUTE(A1," ",REPT(" ",99),
25-LEN(SUBSTITUTE(LEFT(A1,25+1)," ",""))+1),25))&"…"))

This slightly shorter formula (using two less function calls) appears to
do what your formula is doing...

=SUBSTITUTE(LEFT(A1,25)," "&TRIM(RIGHT(SUBSTITUTE
(TRIM(LEFT(A1,25))," ",REPT(" ",99)),99)),"")&"…"


Only slightly shorter if you eliminate my first line, which checks for blanks
and contents that don't need adjustment. And I got some strange results with
this in A1:

123 56789 123 56789 123 56789 123 56789

If you care where it gets chopped:

=IF(A1="","",IF(LEN(A1)<=25,A1,
TRIM(LEFT(SUBSTITUTE(LEFT(A1,25+1)," ",REPT(" ",99)),
LEN(SUBSTITUTE(LEFT(A1,25+1)," ",REPT(" ",99)))-99))&"€¦"))

Change the three 25's for a different length.


A little better:

=IF(A1="","",IF(LEN(A1)<=25,A1,
TRIM(LEFT(SUBSTITUTE(A1," ",REPT(" ",99),
25-LEN(SUBSTITUTE(LEFT(A1,25+1)," ",""))+1),25))&"€¦"))

Hi

OK thanks for this. It works fine and is very neat.

I do find the final three dots do actually make the line longer than the
limit set in the formula , which is a curious anomaly.


Best Wishes

Colin Hayes wrote:

If you care where it gets chopped:

=IF(A1="","",IF(LEN(A1)<=25,A1,
TRIM(LEFT(SUBSTITUTE(LEFT(A1,25+1)," ",REPT(" ",99)),
LEN(SUBSTITUTE(LEFT(A1,25+1)," ",REPT(" ",99)))-99))&"€¦"))

Change the three 25's for a different length.


A little better:

=IF(A1="","",IF(LEN(A1)<=25,A1,
TRIM(LEFT(SUBSTITUTE(A1," ",REPT(" ",99),
25-LEN(SUBSTITUTE(LEFT(A1,25+1)," ",""))+1),25))&"€¦"))

Hi

OK thanks for this. It works fine and is very neat.

I do find the final three dots do actually make the line longer than the
limit set in the formula , which is a curious anomaly.


Best Wishes


You can just drop the &"..." if you want to keep it to a max of 25. I just
added that to give an indication that there was more to the original text.