CODEWITHSUNDEEP
excelvba
Dunno123
Dunno123

Reputation: 69

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

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)?

Upvotes: 1

Views: 1544

Answers (3)

JvdV
JvdV

Reputation: 75930

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:

enter image description here

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:

enter image description here

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

Upvotes: 2

Anabas
Anabas

Reputation: 356

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

Upvotes: 0

Dominik
Dominik

Reputation: 188

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

Upvotes: 0

Related Questions