Excel - VBA : How do I replace the last 3 characters if they are "..."

Question

Please could you help me a little bit? I am a complete beginner, I don't know anything about programming.

I have the following code that changes double spaces into single spaces and deletes "..." if it's at the beginning of the selected cell(s).

Sub Test()
  Dim X As Long, Cell As Range
  For Each Cell In Selection
    For X = Len(Cell.Text) To 1 Step -1
      If Cell.Characters(X - 1, 2).Text = "  " Then Cell.Characters(X, 1).Text = ""
      If Cell.Characters(1, 3).Text = "..." Then Cell.Characters(1, 3).Text = ""
    Next
  Next
End Sub

Please could you tell me how I could change the part If Cell.Characters(1, 3).Text so that it removes "..." if it's at the end of the selected cell(s)?

I have answerd your question with the assumption you are interested in removing `...` from the *end* of your values. However, above your code you mentioned the beginning of the cells. Can you rectify? — JvdV, May 28 '20 at 12:22

JvdV · Answer 1 · 2020-05-28T15:29:32.140

This is not that easy as may seem, since Excel has the inclination to adjust three dots into an ellipsis, making it a single character that's unrecognizable when compared to a dot (or three). Furthermore, you don't need to loop characters 1 by 1, instead you could use Like to check if a cell is ending with the three dots, or rather the ellipsis. Next to that, we can trim excessive space characters in a Range in one go, using Application.Trim() as shown here.

So let's look at example data like:

Then if we select this Range and go over its cells using, for example:

Sub Test()

Dim cl As Range
For Each cl In Selection
    If cl.Value Like "*..." Then
        cl.Value = Left(cl.Value, Len(cl.Value) - 3)
    ElseIf cl.Value Like "*" & ChrW(8230) Then
        cl.Value = Left(cl.Value, Len(cl.Value) - 1)
    End If
Next
Selection.Value = Application.Trim(Selection)

End Sub

The results would then be:

And for the sake of fun alternatives, a RegEx approach:

Sub Test2()

Dim cl As Range
With CreateObject("vbscript.regexp")
    .Global = True
    .Pattern = "…$|\.{3}$"
    For Each cl In Selection
        cl.Value = .Replace(cl.Value, "")
    Next
End With
Selection.Value = Application.Trim(Selection)

End Sub

Thanks for the clarification on the three dots. I was wondering why my answer wasn't working! — Brian M Stafford, May 28 '20 at 12:45
Thank you for your help. It works, but it removes all the formatting in the cell, so I cannot use this :( — Dunno123, May 28 '20 at 21:02
That would have been worth mentioning in your question @Dunno123. — JvdV, May 29 '20 at 07:33

score 0 · Answer 2 · answered May 28 '20 at 12:27

Maybe this can help you: Use the replace methode to change two spaces into one space. To search for three points at the beginning use the left methode and if it's the case, cut it out with the right methode. Here you have to watch out. Excel often replace three point by the character 133. So you have additional to test for it.

Sub Test()
  Dim cell As Range
  For Each cell In Selection

    cell.Value = Replace(cell.Value, "  ", " ")

    If Left(cell.Value, 3) = "..." Then
        cell.Value = Right(cell.Value, Len(cell.Value) - 3)
    End If

    If Left(cell.Value, 1) = Chr(133) Then
        cell.Value = Right(cell.Value, Len(cell.Value) - 1)
    End If

  Next

End Sub

score 0 · Answer 3 · answered May 28 '20 at 13:12

I think you can use Characters(1,3).Insert("") to change the text

Sub Test()
Dim c As Range
Selection.Value = Application.Trim(Selection)
For Each c In Selection
      If c.Characters(1,3).Text = "..." Then c.Characters(1,3).Insert("")
Next
End Sub

Excel - VBA : How do I replace the last 3 characters if they are "..."

3 Answers3