Find and replace values with a dash (-)

zack8576

Active Member
Joined
Dec 27, 2021
Messages
271
Office Version
  1. 365
Platform
  1. Windows
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

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

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Try this.

VBA Code:
Sub CleanUpOutlier()
    With Range("D1", Cells(Rows.Count, "D").End(3))
        .Replace "F12314J-67""", "F12314J"
    End With
End Sub
 
Upvote 0
Solution
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

VBA Code:
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.
 
Upvote 0
I'm wondering if it is just this? Test with a copy

VBA Code:
Sub RemoveDashEtc()
  Columns("D").Replace What:="-*", Replacement:="", LookAt:=xlPart
End Sub
 
Upvote 0
I'm wondering if it is just this? Test with a copy

VBA Code:
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?
 
Upvote 0
I'm wondering if it is just this? Test with a copy

VBA Code:
Sub RemoveDashEtc()
  Columns("D").Replace What:="-*", Replacement:="", LookAt:=xlPart
End Sub
Thank you Peter, will try it out !
 
Upvote 0
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 :)
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,203
Members
452,617
Latest member
Narendra Babu D

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
Back
Top