For the Excel Pro: Cell Formatting Question?

[Ferra]

you know when a text is too long to fit inside a cell, the last portion of the text usually just get cut off...

I am wondering if it is possible to format the cell such that...the last portion of the text becomes "..." or "~" if the text is too long to fit inside the cell.

For example:
If I have this text: "superduperwalala" in cell A, and anything after "superdup" gets cut off...i want it to display something like "superd..." or "superdu~" instead.


**I know I can autofit, resize text, wrap text, merge cell....etc, but I don't want that because I want to keep the format uniform.

[TheNewGirl]

Which version of Excel are you using?

[Ferra]

Originally Posted by TheNewGirl

Which version of Excel are you using?

2007

[TheNewGirl]

Originally Posted by Ferra

2007

Hmmm I don't think you can do it without creating more rows/cols. I suspect you could probably rig up an IF statement based on the total characters and if it exceeds X characters then only show X characters and then hide the original input col but if you expand that collum it won't show any more of the word than the if statement requested and all and all would be way more trouble then it's worth and I can't figure out the formula to do it.

Or you could put a small collum beside the one with the text in it and have it use an =IF(LEN(A1)>8, "…") this would return an ... in the adjacent cell. A1 being what ever the origional cell address is and 8 or what ever being the total number of characters typically visible in the cell, adjust as you like.

[Ferra]

Originally Posted by TheNewGirl

Hmmm I don't think you can do it without creating more rows/cols. I suspect you could probably rig up an IF statement based on the total characters and if it exceeds X characters then only show X characters and then hide the original input col but if you expand that collum it won't show any more of the word than the if statement requested and all and all would be way more trouble then it's worth and I can't figure out the formula to do it.

so can I do that in office 2010? (I was thinking about upgrading anyway)

I actually have no problem writing the forumla like what you describle...only problem with manual formula is that... everytime I change the font/size, column width, i will have to re-write the formula again.. (plus the formula will be based on word counts, but not every word/numbers/space take up the same width)

[Wetordry]

Start a new column:

=LEFT(

Then you can have uniform cell formatting by specifying # of characters

[TheNewGirl]

I don't know about 2010 I only have 2007,

Wetordy, I tried a variation of that with data in a column then I fed it into a new one that went =left(A1,5) for example which will pull the first 5 characters of cell A1, is there a way to tag text onto the end of it? I tried adding "..." but that won't do it and going into the help sets me into a treaty on left to right justification of language. :P

Ferra > You will not be able to do it on Word count. If there's away to do it at all it'd be through character count which you could base on a variable in a second box so you only have to change it in one place and it'll change them all. Still it's more static then you want.

[InvisibleSoul]

=CONCATENATE(LEFT(A1,5),"…")

or

=LEFT(A1,5)&"…"

[Ferra]

i actually meant "character" count on my last post

I used invisiblesoul method + a "=if((len(A1)>8, LEFT(A1,5)&"…", A1)
(otherwise the formula would've added the "..." at the back regardless of how long the word is.

Not perfect but i guess that as good as it gets....thanks!!