CODEWITHSUNDEEP
regexvbaexcelreplace
NRGdallas
NRGdallas

Reputation: 395

excel regex end of line

I am looking for a regex for excel 2007 that can replace all instances of -3 ONLY at the end of the string, replacing it with absolutely nothing (removing it). There are instances of -3 throughout the strings, however I need to remove only the ones at the end. This is being integrated into a macro, so find and replace using a single regex is preferred.

Upvotes: 0

Views: 2051

Answers (3)

Peter Albert
Peter Albert

Reputation: 17495

Unless its part of a bigger macro, there's no need for VBA here! Simply use this formula and you'll get the result:

=IF(RIGHT(A1,2)="-3",LEFT(A1,LEN(A1)-2),A1)

(assuming that your text is in cell A1)

Upvotes: 1

Scott Holtzman
Scott Holtzman

Reputation: 27259

You can do this without Regex by using VBA's Instr function. Here is the code:

Sub ReplaceIt()

Dim myRng As Range
myRange = Range("A1") ' change as needed

If InStr(Len(myRange.Text) - 2, myRange.Text, "-3") > 0 Then

    myRange.Value = Left(myRange, Len(myRange) - 2)

End If


End Sub

Update

Based on Juri's comment below, changing the If statement to this will also work, and it's a bit cleaner.

If Right (MyRange, 2) = "-3" Then MyRange=Left(MyRange, Len(MyRange)-2)

Upvotes: 1

bonCodigo
bonCodigo

Reputation: 14361

Please try the following:-

Edit as per OP's comments:

Sub mymacro()
Dim myString as String
 //'--do stuff
 //'-- you could just do this or save the returning 
 //'-- string to another string for further processing :)
 MsgBox replaceAllNeg3s(myString)
End Sub

Function replaceAllNeg3s(ByRef urstring As String) As String
Dim regex As Object
Dim strtxt As String

  strtxt = urstring
  Set regex = CreateObject("VBScript.RegExp")

  With regex
    //'-- replace all -3s at the end of the String
    .Pattern = "[(-3)]+$"
    .Global = True
    If .test(strtxt) Then
      //'-- ContainsAMatch = Left(strText,Len(strText)-2)
      //'-- infact you can use replace
      replaceAllNeg3s = Trim(.Replace(strText,""))
    Else
      replaceAllNeg3s = strText
    End If
  End With

End Function

//'-- tested for 
//'-- e.g. thistr25ing is -3-3-3-3
//'-- e.g. 25this-3stringis25someting-3-3
//'-- e.g. this-3-3-3stringis25something-5
//'-- e.g. -3this-3-3-3stringis25something-3

Upvotes: 1

Related Questions