IF Condition for Two Different Scenarios

[madhuchelliah]

Hello Guys, I have two scenarios need to be solve with IF conditions. In my sheet1 I column cells i have a string which consists of alphanumeric and symbols. I am in the process of cleaning up the strings. I cant fix these 2 cases. Please heads up and help.

1st case: If any cell in I column has "A" as second word, the space between 1st and 2nd word should be deleted. Example: 70 A to 70A) Note: Second word is "A" letter alone not words starts with A.

2nd case: If any of the cell in in I column starts with "NAME" the first word below to that cell should be deleted.

I need 2 separate macros for the 2 cases. Thank you.

 

[reberryjr]

In your first scenario, do you have instances where there would be data followed by a space, followed by an A, that you don't want to delete the space?

For your second scenario, you don't actually need VBA. You can use a formula:

NAME
Purple	Delete	=IF(A1="NAME","Delete","")
Dog
Cat
NAME
Fish	Delete

<tbody>
</tbody>

 

[offthelip]

This code will deal with your first condition, your second requirement is unclear what does:

the first word below to that cell should be deleted.

mean??

code for first requirement

Sub test()

Dim txt As String


lastrow = Cells(Rows.Count, "I").End(xlUp).Row
inarr = Range(Cells(1, 9), Cells(lastrow, 9))
 For i = 1 To lastrow
  txt = inarr(i, 1)
  ' find the first space
  sp = InStr(txt, (" "))
  If sp > 0 Then
  If Mid(txt, sp + 1, 1) = "A" Then
   inarr(i, 1) = Left(txt, sp - 1) & Mid(txt, sp + 1)
  End If
  End If
Next i
Range(Cells(1, 9), Cells(lastrow, 9)) = inarr


   
End Sub

 

[madhuchelliah]

Hello Offthelip, the code is working great. Thank you. Please check the below table for my 2nd case. If any of the cell in I column starts with NAME the first word in the cell below to that should get deleted. Hope you understand. Please help. Thank you.

I		I
NAME AGE		NAME AGE
Mr John		John
Test		Test
NAME DOB		NAME DOB
AA Sara		Sara
Tst		Tst
NAME MARK		NAME MARK
Adam Smith		Smith

<tbody>
</tbody>

 

[madhuchelliah]

Hello Reberry, thanks for your formula. Since i am running batch of macros in the process. So i required in macros.

 

[Nishant94]

Check if this is what you want:

Sub madhuchelliah()


Dim lastrow As Integer, i As Integer
Dim MyRng As Range
Dim Rng As Range
Dim ChkVal As Variant
Dim text As String


lastrow = ActiveSheet.Cells(Rows.Count, 9).End(xlUp).Row
Set MyRng = ActiveSheet.Range("I1:I" & lastrow)


For Each Rng In MyRng
    
    ChkVal = Split(Rng.Value, " ")
    If UBound(ChkVal) > 0 Then
        If ChkVal(1) = "A" Then
            text = ChkVal(0) & ChkVal(1)
            Rng.Value = text & Replace(Rng.Value, ChkVal(0) & " " & ChkVal(1), "")
         End If
    End If
        
    If Rng.Row <> 1 Then


        If InStr(LCase(Rng.Offset(-1, 0).Value), "name") <> 0 Then
            Rng.Value = Mid(Rng.Value, InStr(Rng.Value, " ") + 1, Len(Rng.Value))
        End If
    
    End If


Next Rng
   
End Sub

 

[madhuchelliah]

Thanks Nishant. Working great!!!!!!!!!!!!!!!