Add " ..." to end of string

[nkitchen31]

Please Login or Register  to view this content.

This is basically the idea of what I need done in VBA.

Essentially, I have cells O4 through O34 with text in them. If the text exceeds 18 characters, I want the 3 dots to be placed at the end of the 15th character in the cell. This way at quick glance I can see that there is more text in there, then I can click on it and read the entire text in the function bar above. The problem, obviously, is that I cannot have this formula/function in the same cell as where I will be typing the text. For the above formula, I have it in another cell just to be tested, and it works good. If I can have the same formula/function written in VBA (I think), this would solve that problem.

I have attached the file for reference.

Thanks in advance!

[jindon]

One way

Please Login or Register  to view this content.

[nkitchen31]

I will try this... Do I add a Module? Or do I put this code in the "Sheet 1" in the Excel Objects list?

Thank you!

Okay, I put it in a "Module" and what happened was it literally CHANGED the text in the cell to "Taxi, Floppies, …"
- What I want is it to just show me in the cell "Taxi, Floppies, …", but when I look in the formula bar, it will show me the entire text string: "Taxi, Floppies, Taxi back from floppies"

Thanks again!

[jindon]

?

What do you really want to do?

Do you want to place the formula?

[nkitchen31]

If you look at the spreadsheet I attached: Find O20. Notice how the text is longer than the width of the cell. What I want is after the 15th or so character to show "..." at the end. This way, at quick glance I can see that there is more in that cell. (So it would look like O21). But when I click on the cell, I want to be able to see the entire text in the "formula bar". The code you gave me literally changes the text to "Taxi, Floppies, ..." so it basically deleted everything after the "...", which is not what I want. Does this make sense now?

Thank you,
Nick

[jindon]

OK, now I understand, however, I don't think it is possible.

Keeping value of full length of the string while appears in cell as different contents.
If someone can come up with the special cell formatting, I'd like to see it, too....

[nkitchen31]

Okay, well thank you for your time and help.

[Marcol]

Why not just use conditional formatting?
Range O15:O33

Please Login or Register  to view this content.

Format
Your choice of cell colour.

[nkitchen31]

Marcol... I wish it was that easy... That's where I started earlier today. Problem is, I can change the "format" to custom and do @ "..." but it adds it to the very end of the text, which is hidden in the cell so you can't see it anyway. What I need it to add the "..." after like the 10th character.

This is where I stand now:

In Conditional I have this: =IF(LEN($O$3)>15, (LEFT($O$3,10) & " …"), $O$3) and the custom format of @ "..."

That isn't working... HOWEVER, this works: =LEN($O$3)>15 in the condition, and under the custom format I have "..." @
That is better than nothing.. the dots are placed in front of the text, which works, but I'd prefer to have the "..." after the 10th character.

So, technically jindon, the cell is showing different text than the formula bar is showing.

Please see new xls attached for reference. Look at the January 2013 sheet cells O3 and O4

Thanks!

[Marcol]

I'm totally lost with your logic ...

If you use the C/F I suggested as the first condition, and use say, "yellow", as the cell colour, then ...

This way, at quick glance (you) can see that there is more in that cell.

This works regardless of column width.

Then if you select any yellow cell you will see exactly what is in the cell in the formula bar.

But when I click on the cell, I want to be able to see the entire text in the "formula bar".

Why would you want to do/see anything different?

What am i missing?

[nkitchen31]

Because I don't want to change the cell color or font color... I want to add three dots to the end of 10th character in the cell. Almost acting like the [More...] function with wordpress or something.

[jindon]

Alternatively, you could use Comments...

[nkitchen31]

I'm getting somewhere.. in the conditional formula I used =LEN($O$4)>15

and in the Format Cells under Number, Custom, Type I have:

_____________ "..." _____________) @

Now the entire cell is blank except for ... in the middle of the cell. When I click the cell, in the formula bar, it shows my original text. I'm certain there is a way to add the ... after the 10th character. I'm on a mission now!

[nkitchen31]

jindon:

This is for you. I didn't quite figure out exactly what I was trying to do, however - this is as close as I'm going to get today I think. I think you'll like it since you wanted to see a cell text different than the formula bar shows.

OK, now I understand, however, I don't think it is possible.

Keeping value of full length of the string while appears in cell as different contents.
If someone can come up with the special cell formatting, I'd like to see it, too....

Please see attached... Look at the column O, specifically O19... now click on it.

[jindon]

O19?

If you clear P19...

[nkitchen31]

Sorry, I uploaded the wrong one... here it is now.

If there is ever anything in O column, P will NEVER be empty. My point is, if you look at O19, it says "More Receipts..." but if you CLICK on the actual cell, look in the formula bar.. it says "Taxi, Floppies, Taxi to get floppies, Taxi after floppies"

[jindon]

Hummmm

Should be OK, if you are happy with it...

[Marcol]

See if this in the Workbook Module gives you some more ideas

Please Login or Register  to view this content.