Auto Replace Text after Pasting

[shanenavy26]

When I "Paste as Value". I would like to have the text automatically replace itself to something new in the same cell.

IE: "POS DEBIT - MCDONALDS" --> *Paste into cell* --> "MCDONALDS".

I cannot seem to find a function to fit this once I paste it. Is there a VBA script to help? I will be pasting all these to Column B.

 

[shanenavy26]

Hi FLuff, I pasted this into the correct sheet, however nothing happened, even after a reload of the document. I am a noob with VBA so am i doing something wrong?

Whilst you have mentioned it on ExcelForum, you made no mention of it on this site & we do not prevent members from posting links.

That said, try

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Cl As Range
   If Not Target.Column = 2 Then Exit Sub
   For Each Cl In Intersect(Target, Range("B:B"))
      If InStr(1, Cl, "- ") > 0 Then Cl.Value = Split(Cl.Value, "- ")(1)
   Next Cl
End Sub

This needs to go in the relevant sheet module

 

[shanenavy26]

Hi Joe, I made the macro but how can I get it to run not on a keyset but on an action like say "PasteAsValue"

I am not sure why you re-posted this question again. Please do not do that, that is against forum rules. I have merged your two threads together.


There is nothing prohibiting links from other websites (as you can see, Fluff already did that in this thread). You just cannot edit your posts yet (and even when you are no longer a noob and have the ability to, there is only a 10 minute window to do so). So you would just need to add it in a reply.


You already did the hard work. All you need to do is turn on the Macro Recorder and record yourself performing those steps above manually (and be sure to select the "Replace All" option).
Then, stop your Macro Recorder, and voila, you have the VBA code that you need to do this automatically.

 

[Fluff]

Can you post your recorded macro?

 

[shanenavy26]

Macro:

Sub replace()
'
' Replace Macro
' Macro recorded 2018/09/30 by User
'


'
Selection.replace What:="ACH Transaction - ", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, MatchByte:=False, SearchFormat:=True, ReplaceFormat:=True
Selection.replace What:="POS Debit - Visa Check Card XXXX - ", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, MatchByte:=False, SearchFormat:=True, ReplaceFormat:=True
Selection.replace What:="POS Debit - Visa Check Card XXXX - ", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, MatchByte:=False, SearchFormat:=True, ReplaceFormat:=True
End Sub

The macro wont run for some reason, I figured if i can get it to work properly, maybe setting a VBA code to run Macro once cell B3 is altered?

 

[Fluff]

If your recorded macro does what you need, try

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Target.Column = 2 Then Exit Sub
   Target.Replace "ACH Transaction - ", "", xlPart, , False, , False, False
   Target.Replace "POS Debit - Visa Check Card XXXX - ", "", xlPart, , False, , False, False
End Sub

Right click the tab you want this to work on > View code > paste this into the window that opens. If you already have code in that window, post it here as they will need to be amalgamated.

 

[shanenavy26]

Is there a way to share a sample fire on here?

The Macro I created does not run after hitting the Shortcut Key I set, im not sure why. As well as your code did not start anything either, was I supposed to fill anything in or press any certain button?

If your recorded macro does what you need, try

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Target.Column = 2 Then Exit Sub
   Target.Replace "ACH Transaction - ", "", xlPart, , False, , False, False
   Target.Replace "POS Debit - Visa Check Card XXXX - ", "", xlPart, , False, , False, False
End Sub

Right click the tab you want this to work on > View code > paste this into the window that opens. If you already have code in that window, post it here as they will need to be amalgamated.

 

[Fluff]

The code should run automatically whenever you change a value in col B.
You cannot upload files to this site, but you could upload to a share site such as OneDrive, DropBox, GoogleDrive, mark for sharing & post the link to the thread.

 

[shanenavy26]

https://www.dropbox.com/s/jsr6b6228qug00m/Example Sheet.xlsm?dl=0

Here is the file. The code you wrote works like you said. Is there a way to get it to do that after I "PasteAsValue"? That way it changes all of them at once as soon as I move them over to the Sheet2.

In the file I shared, its the data in Sheet one that I need to paste over into Sheet 2. And when id do that, Id like it to do what your code did, but for all of them.

 

[Fluff]

Ok, thanks for the file. If you remove the code you've got on the entry sheet & replace it with this

Private Sub Worksheet_Change(ByVal Target As Range)
   Target.Replace "ACH Transaction - ", "", xlPart, , False, , False, False
   Target.Replace "POS Debit - Visa Check Card XXXX - ", "", xlPart, , False, , False, False
End Sub

 

[shanenavy26]

This is perfect, exactly what I needed! Thank you very much for your help!

Ok, thanks for the file. If you remove the code you've got on the entry sheet & replace it with this

Private Sub Worksheet_Change(ByVal Target As Range)
   Target.Replace "ACH Transaction - ", "", xlPart, , False, , False, False
   Target.Replace "POS Debit - Visa Check Card XXXX - ", "", xlPart, , False, , False, False
End Sub