VBA Code to remove trailing "/"

[MHamid]

Hello,

I am trying to figure out a code that will remove the trailing "/" from a string. I haven't found a code that I can use for my situation.
The range is in column AR3:AR (from row 3 to last row with data). My current data in column AR has values such as "Business/Operations/" and I am looking to remove the trailing "/' if there is any within the range.
Any assistance will be greatly appreciated.


Thank you,

 

[Legacy 143009]

Sub test()
  For Each rng In Range("AR:AR")
    If Right(Trim(rng.Value), 1) = "/" Then
      rng.Value = Left(Trim(rng.Value), Len(Trim(rng.Value))-1)
    End If
  Next
End Sub

 

[MHamid]

Sub test()
  For Each rng In Range("AR:AR")
    If Right(Trim(rng.Value), 1) = "/" Then
      rng.Value = Left(Trim(rng.Value), Len(Trim(rng.Value))-1)
    End If
  Next
End Sub

Awesome! Thank you! I just realized that I also will need to remove the "/" from the beginning of the string as well. Some cell begin with "/" and I need that removed as well. Do I need to just copy this same code and replace the right with left function?

 

[kevin9999]

Another option?

Sub Clean()
    With Range("AR3:AR" & Cells(Rows.Count, "AR").End(xlUp).Row)
        .Replace "/", "", xlPart
    End With
End Sub

 

[Legacy 143009]

Awesome! Thank you! I just realized that I also will need to remove the "/" from the beginning of the string as well. Some cell begin with "/" and I need that removed as well. Do I need to just copy this same code and replace the right with left function?

Yes, Left condition will look like that:

Sub test()
  For Each rng In Range("AR:AR")
    If Right(Trim(rng.Value), 1) = "/" Then
      rng.Value = Left(Trim(rng.Value), Len(Trim(rng.Value))-1)
    End If
    If Left(Trim(rng.Value), 1) = "/" Then
      rng.Value = Right(Trim(rng.Value), Len(Trim(rng.Value))-1)
    End If
  Next
End Sub

 

[MHamid]

Another option?

Sub Clean()
    With Range("AR3:AR" & Cells(Rows.Count, "AR").End(xlUp).Row)
        .Replace "/", "", xlPart
    End With
End Sub

Thank you Kevin, but I just need to remove any "/" at the beginning and end of a string. I need to keep the ones in between the string.

 

[MHamid]

Yes, Left condition will look like that:

Sub test()
  For Each rng In Range("AR:AR")
    If Right(Trim(rng.Value), 1) = "/" Then
      rng.Value = Left(Trim(rng.Value), Len(Trim(rng.Value))-1)
    End If
    If Left(Trim(rng.Value), 1) = "/" Then
      rng.Value = Right(Trim(rng.Value), Len(Trim(rng.Value))-1)
    End If
  Next
End Sub

Thank you for confirming Flash ... is exactly what I need.

 

[Legacy 143009]

Glad it did work! Thanks for the feedback

 

[kevin9999]

Thank you Kevin, but I just need to remove any "/" at the beginning and end of a string. I need to keep the ones in between the string.

Understood, and thanks for the feedback