Find and replace values with a dash (-)

[zack8576]

I have a bunch of excel files that contain some values in column D
Whenever there are values in this format, with a dash: F12314J-67" (yes with an inch mark at the very end), they need to be replaced with something like : F12314J
I've been working on this for a while now and my code so far is not working. Below is what I have so far

Sub CleanUpOutlier()
    Set rng2 = Range("A1").CurrentRegion
    lr9 = rng2.Cells(Rows.Count, "D").End(3).Row
        .Replace "F12314J-67", "F12314J", LookAt:=xlWhole
End Sub

 

[kevin9999]

Try this.

Sub CleanUpOutlier()
    With Range("D1", Cells(Rows.Count, "D").End(3))
        .Replace "F12314J-67""", "F12314J"
    End With
End Sub

 

[SanjayGMusafir]

I have a bunch of excel files that contain some values in column D
Whenever there are values in this format, with a dash: F12314J-67" (yes with an inch mark at the very end), they need to be replaced with something like : F12314J
I've been working on this for a while now and my code so far is not working. Below is what I have so far

Sub CleanUpOutlier()
    Set rng2 = Range("A1").CurrentRegion
    lr9 = rng2.Cells(Rows.Count, "D").End(3).Row
        .Replace "F12314J-67", "F12314J", LookAt:=xlWhole
End Sub

If it's a one time issue you can correct that using split columns with delimiter DASH and then Paste values back

That would be easier

If it's a continuous/regular process then you should go for VBA

Also, I'm not sure if Power Pivot works with Excel 365. If so use that to ease up this regular process.

 

[Peter_SSs]

I'm wondering if it is just this? Test with a copy

Sub RemoveDashEtc()
  Columns("D").Replace What:="-*", Replacement:="", LookAt:=xlPart
End Sub

 

[SanjayGMusafir]

I'm wondering if it is just this? Test with a copy

Sub RemoveDashEtc()
  Columns("D").Replace What:="-*", Replacement:="", LookAt:=xlPart
End Sub

Just for my understanding, since I was following this thread - can we use * in VBA too?

 

[zack8576]

I'm wondering if it is just this? Test with a copy

Sub RemoveDashEtc()
  Columns("D").Replace What:="-*", Replacement:="", LookAt:=xlPart
End Sub

Thank you Peter, will try it out !

 

[zack8576]

Just for my understanding, since I was following this thread - can we use * in VBA too?

If it's a one time issue you can correct that using split columns with delimiter DASH and then Paste values back

That would be easier

If it's a continuous/regular process then you should go for VBA

Also, I'm not sure if Power Pivot works with Excel 365. If so use that to ease up this regular process.

Thank you Sanjay, there are thousands of excel files that need to be cleaned, so it will need to be VBA

 

[zack8576]

Try this.

Sub CleanUpOutlier()
    With Range("D1", Cells(Rows.Count, "D").End(3))
        .Replace "F12314J-67""", "F12314J"
    End With
End Sub

Kevin, thank you so much ! this worked like a charm

 

[kevin9999]

Kevin, thank you so much ! this worked like a charm

You're welcome & thanks for the feedback

 

[Peter_SSs]

Just for my understanding, since I was following this thread - can we use * in VBA too?

In some circumstances.