Advanced Find and Replace VBA

madhuchelliah

Board Regular
Joined
Nov 22, 2017
Messages
168
Office Version
  1. 2013
Platform
  1. Windows
Hello all, i have to find a particular characters from the string and it has to be replaced with none. I have string which is combination of letter, symbols, spaces and numbers in I column. From the cell I4 the code has to read the content before the first instance of "-" high-fun and replace that content with none in all the cells starting with that content including I4. Please go through the example for better understanding. In I4 before the first instance of the high fun, the content is 25 B. I8 also starting with similar content. The code has to read that 25 B and delete from both the cells I4 and I8. The column range is dynamic and the content might be available at nth times. Thank you
I
1XXXXX AAAA 645A-sd
2
3YYYY
425 B -1 XXX SSSSS 236-235
502 ghfgdhfg fdfdd fdfd
650 ds ssdss sdsdsds
7-50A ff ff
825 B 25 sdfsf fssdfs sfsf
9sd sdsds sdsds*ssd
10hgfhfh / gdf + dff5
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Try this

VBA Code:
Sub Find_Replace()
  Dim a As Variant, s As String
  Dim i As Long
  a = Range("I4", Range("I" & Rows.Count).End(3)).Value2
  
  For i = 1 To UBound(a)
    If InStr(1, a(i, 1), "-") > 0 And s = "" Then
      s = WorksheetFunction.Trim(Split(a(i, 1), "-")(0))
    End If
    If s <> "" And Left(a(i, 1), Len(s)) = s Then
      a(i, 1) = Mid(a(i, 1), Len(s) + 1)
    End If
  Next
  Range("I4").Resize(UBound(a)).Value = a
End Sub
 

madhuchelliah

Board Regular
Joined
Nov 22, 2017
Messages
168
Office Version
  1. 2013
Platform
  1. Windows
Try this

VBA Code:
Sub Find_Replace()
  Dim a As Variant, s As String
  Dim i As Long
  a = Range("I4", Range("I" & Rows.Count).End(3)).Value2
 
  For i = 1 To UBound(a)
    If InStr(1, a(i, 1), "-") > 0 And s = "" Then
      s = WorksheetFunction.Trim(Split(a(i, 1), "-")(0))
    End If
    If s <> "" And Left(a(i, 1), Len(s)) = s Then
      a(i, 1) = Mid(a(i, 1), Len(s) + 1)
    End If
  Next
  Range("I4").Resize(UBound(a)).Value = a
End Sub
Hello Dan, thanks for your solution. It is working as expected. But a small change in the requirement. As per old requirement, the content has to be replaced by none in any available cell.Instead of that, the content should be replaced only if the above cell of the content starts with "Date". Please see the example for better understanding. For Cells I4, I8 and I16, the above cell is start with date so it has to be replaced the content with none. For cell I11 th above cell is not starts with date so it has to be untouched. Sorry for the inconvenience. Thanks again. Stay safe.


Book4.xlsm
I
1XXXXX AAAA 645A-sd
2ererree erer/* *595
3Date. Sds dsdsd dsds@=*
425 B -1 XXX SSSSS 236-235
502 ghfgdhfg fdfdd fdfd
650 ds ssdss sdsdsds
7Date. Sssd sdsdds
825 B 25 sdfsf fssdfs sfsf
9sd sdsds sdsds*ssd
10hgfhfh / gdf + dff5
1125 B 25 sdfsf fssdfs sfsf
12sd sdsds sdsds*ssd
13hgfhfh / gdf + dff5
1402 ghfgdhfg fdfdd fdfd
15Date. 50 ds ssdss sdsdsds
1625 B 25 sdfsf fssdfs sfsf
Sheet1
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Try this

VBA Code:
Sub Find_Replace()
  Dim a As Variant, s As String
  Dim i As Long
  a = Range("I3", Range("I" & Rows.Count).End(3)).Value2
  
  For i = 2 To UBound(a)
    If InStr(1, a(i, 1), "-") > 0 And s = "" Then
      s = Split(a(i, 1), "-")(0)
    End If
    If s <> "" And Left(a(i, 1), Len(s)) = s And Left(a(i - 1, 1), 4) = "Date" Then
      a(i, 1) = Mid(a(i, 1), Len(s) + 1)
    End If
  Next
  Range("I3").Resize(UBound(a)).Value = a
End Sub
 

madhuchelliah

Board Regular
Joined
Nov 22, 2017
Messages
168
Office Version
  1. 2013
Platform
  1. Windows
Try this

VBA Code:
Sub Find_Replace()
  Dim a As Variant, s As String
  Dim i As Long
  a = Range("I3", Range("I" & Rows.Count).End(3)).Value2
 
  For i = 2 To UBound(a)
    If InStr(1, a(i, 1), "-") > 0 And s = "" Then
      s = Split(a(i, 1), "-")(0)
    End If
    If s <> "" And Left(a(i, 1), Len(s)) = s And Left(a(i - 1, 1), 4) = "Date" Then
      a(i, 1) = Mid(a(i, 1), Len(s) + 1)
    End If
  Next
  Range("I3").Resize(UBound(a)).Value = a
End Sub
Hey Dan, that's super cool. Working great !!!. Thank you. Stay safe.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,241
Messages
5,546,688
Members
410,755
Latest member
sompongt
Top