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.

 

[RileyC]

Why not just modify the values after? I.E. run a function on the column next to it which removed POS DEBIT -

 

[Fluff]

Do you always want whatever value occurs after the " - "

 

[shanenavy26]

Auto Replace Text VBA?

Given a bunch of cells named "POS Debit - Expense Name Here" and I want to remove the "POS Debit - " From every entry to just leave the Expense Name. Is there a way in VBA or a native method?
Find What Replace With
Manual method: HOME Tab -> Find&Select (or Ctrl+F) -----> Replace --> "POD Debit - " "" (Leaving it blank)

I would like this to be automatic, after PASTING the information into a cell, or a way to click a button and change it. (As automatic as possible)



Thank you, and I have tried using AutoCorrect settings, it doesn't do it automatically.​

 

[BarryL]

Re: Auto Replace Text VBA?

double post https://www.mrexcel.com/forum/excel-questions/1072283-auto-replace-text-after-pasting.html

 

[Fluff]

Cross posted https://www.excelforum.com/excel-general/1247266-auto-replace-text-vba.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[shanenavy26]

Do you always want whatever value occurs after the " - "

Correct

 

[shanenavy26]

Cross posted https://www.excelforum.com/excel-general/1247266-auto-replace-text-vba.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

I tried to insert the Link, it wouldn't let me insert links to another website unfortunately, so I mentioned I was crossposting. my apologies.

 

[Joe4]

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.

I tried to insert the Link, it wouldn't let me insert links to another website unfortunately, so I mentioned I was crossposting. my apologies.

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.

Find What Replace With
Manual method: HOME Tab -> Find&Select (or Ctrl+F) -----> Replace --> "POD Debit - " "" (Leaving it blank)

I would like this to be automatic, after PASTING the information into a cell, or a way to click a button and change it. (As automatic as possible)

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]

I tried to insert the Link, it wouldn't let me insert links to another website unfortunately, so I mentioned I was crossposting. my apologies.

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