CODEWITHSUNDEEP
vbaexcelcommand
Asen Panayotov
Asen Panayotov

Reputation: 3

VBA: Delete everything before character including the character

I have excel sheet with a lot of data, that need to be parsed. It is a big project for me, managed to make it step by step so far, but something small is stopping me. In one of my fields i have: 

sadadasd|2|adsgfas
sadadasd|1|adsgfas 
sadadasd|0|adsgfas

What i want to do is for the rows that contain 

|1| -> delete entire row
|2| -> delete everything before |1| including |1|
|0| -> Delete cell value.

I have the conditions for 0 and 2, but im missing 1. I am currently using the following code:

Dim myRange As Range
Dim myCell As Range
Set myRange = Range("H4:H1000")
For Each myCell In myRange
If myCell Like "*|0|*" Then

myCell.ClearContents


ElseIf myCell Like "*|1|*" Then
myCell.EntireRow.Delete

ElseIf myCell Like "*|2|*" Then
  result_3

Else
End If
Next myCell

Can you help me replacing result_3 with a command for removing everything except the text in the back? I'm sure there is a solution already, but I spent too much time on that one, and I need to finish the project soon. Thank you in advance!

Upvotes: 0

Views: 7171

Answers (2)

Gary's Student
Gary's Student

Reputation: 96753

Give this a try:

Sub qwerty()
    Dim i As Long, v As String, vv As String

    For i = 1000 To 4 Step -1
        With Cells(i, "H")
            v = .Value
            vv = Mid(v, 10, 2)
            Select Case vv
            Case "2|"
                .EntireRow.Delete
            Case "1|"
                .Value = Mid(v, 12, 9999)
            Case "0|"
                .Clear
            End Select
        End With
    Next i
End Sub

If your records are not fixed length:

Sub qwerty()
    Dim i As Long, v As String

    For i = 1000 To 4 Step -1
        With Cells(i, "H")
            v = .Value
            If InStr(1, v, "|") > 0 Then
                Select Case Split(v, "|")(1)
                Case "2"
                    .EntireRow.Delete
                Case "1"
                    .Value = Split(v, "|")(2)
                Case "0"
                    .Clear
                End Select
            End If
        End With
    Next i
End Sub

Upvotes: 1

Miguel_Ryu
Miguel_Ryu

Reputation: 1418

To remove everything before the number 1 including the '|' chars.

myCell.Value = Right(myCell.Value, Len(myCell.Value) - InStr(myCell.Value, "1") + 1)

Btw from your explanation of the problem it seems that you are mixing the solution for *|1|* and *|2|*.

Upvotes: 0

Related Questions