Macro to delete line in excel after third / sign.

[adrian123]

Hi All,

I am looking for macro to delete all lines in excel based on data stored in column A.
In column A I have data in the following format:

/text1/text2/text3/text4/text5/text6
/text1/text2/text3/text4/text5
/tex1
/text1/text2
/text1/text2/text3

I have to delete all lines in excel where there is data after 3 rd /, so the result after deletion should be:

/text1
/text1/text2
/text1/text2/text3

text in each case can be different lenght but deletion should be always after 3 rd / keeping text3

Thank you for helping me

[大灰狼1976]

Hi adrian!
something like below:

Sub Test()
Dim rng As Range, i&, arr
For i = 1 To [a65536].End(3).Row
arr = Split(Cells(i, 1), "/")
If UBound(arr) > 3 Then
If rng Is Nothing Then Set rng = Rows(i) Else Set rng = Union(rng, Rows(i))
End If
Next i
If Not rng is Nothing Then rng.Delete
End Sub

[rothstein]

Here is another macro to consider...



Sub RemoveCellsWithFourOrMoreSlashes()
With Range("A1", Cells(Rows.Count, "A").End(xlUp))
.Value = Evaluate(Replace("IF(@="""","""",IF(@=SUBSTITUTE(@,""/"","""",4),@,""""))", "@", .Address))
.SpecialCells(xlBlanks).EntireRow.Delete
End With
End Sub