Restricting Number of characters in a cell

[Colin Hayes]

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

 

[Luke M]

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

alternatively:
=RIGHT(A2,25)

 

[Glenn]

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]

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]

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.

 

[Rick Rothstein]

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 Rothstein]

I need to trim the contents of a cell to 25 characters. It doesn't

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.

 

[Glenn]

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

 

[Colin Hayes]

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

 

[Glenn]

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.