Consectutive dots treated as one character

[chrisinparra]

I have a formula which returns a preview of a larger string. For example, the full string is "Customer has called twice complaining about his package not arriving."

I use a combination of the LEN and LEFT functions to return a preview of this string for the user. This might appear as "Customer has called twi...". I add the 3 dots on the end so the user knows there is more text in the string.

I then want to display a message box with the full string when the user selects the cell. That is, user clicks on cell containing "Customer has called twi..." and a message box comes up showing "Customer has called twice complaining about his package not arriving."

I only want the message box to appear if the last 3 characters of the preview are "..." . So I put a condition in the macro that calls the message box to check for this.

Sub show_notes()
preview_notes = ActiveCell.Value
preview_notes_suffix = Right(preview_notes,1) 'neither 3 nor 1 works here

If preview_notes_suffix = "..." Then
msgbox notes 'where notes is a variable containing the full string
Else
End If
End Sub

I can never get the preview_notes_suffix to equal "..." if I am pulling preview_notes from a cell value. If I set
preview_notes = "Customer has called twi..."
preview_notes_suffix = Right(preview_notes,3)
then it works.

How can I check if the last 3 characters of a cell value = "..." ?

Further to this, I did some testing and found the following results for the below formulas (VBA appears to be doing different things depending on whether the string is hard-coded in the macro or if I pull the value from a cell):
=RIGHT("...",3) returns "..."
=RIGHT("...",2) returns "..."
=RIGHT("...",1) returns "..."
=RIGHT("..",2) returns ".."
=RIGHT("..",1) returns "."
=RIGHT("....",4) returns "...."
=RIGHT("....",3) returns "...."
=RIGHT("....",2) returns "...."
=RIGHT("....",1) returns ".

 

[Larena]

I just had a play with this, and found that "..." as typed into the macro is not actually the … characters that are displayed in the worksheet. If you type ... into a cell in Excel, then copy it from the formula bar into the macro it should work.

 

[macropod]

That's because three periods in vba is not the same as an ellipsis (i.e. … = Chr(133)) in the application.

Cheers
Paul Edstein
[MS MVP - Word]

 

[MakeItSo]

Turn off Autocorrect for this.
Steps to do this (translated from German Excel, so might not be 100% correct):
=>File=>Options=>Document checking=>Autocorrect settings
Find the three dot entry in the list box at the bottom of the dialog, remove the three dot entry.

Cheers,
MakeItSo

“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.