Advanced Find and Replace VBA

madhuchelliah

Board Regular
Joined
Nov 22, 2017
Messages
211
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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
14,792
Office Version
  1. 2010
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
211
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
14,792
Office Version
  1. 2010
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
 
Solution

madhuchelliah

Board Regular
Joined
Nov 22, 2017
Messages
211
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
14,792
Office Version
  1. 2010
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,118
Messages
5,835,492
Members
430,359
Latest member
ManuelCukRia

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top