Restricting Number of characters in a cell
 

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

Restricting Number of characters in a cell
 

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

Restricting Number of characters in a cell
 

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.

Restricting Number of characters in a cell
 

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))&"…"))

Restricting Number of characters in a cell
 

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.

Restricting Number of characters in a cell
 

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

Restricting Number of characters in a cell
 

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)

Restricting Number of characters in a cell
 

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

Restricting Number of characters in a cell
 

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

Restricting Number of characters in a cell
 

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.