Need VBA Code to remove number prefix if name found on another list

I have a challenge removing leading numbers in a list. Originally, I assume the leading number was always 5 digits in length but it turns our it can be any length. So, I need to take a different approach. I again have attached a sample.

In the sample the tab named “Names” data on what to adjust based on what is found in the “Data” Tab. What I am looking to have the VBA code do is remove a number on the front of the name on the “Names” tab if the same name if found in the list of names in cells E2:E in the “Data” tab. I want the VBA code to walk down the list of names on the “Name” Tab starting at B5 and compare it to the names found on the Data Tab cells E2:E. I have created the “Names (desired result)” Tab to show you the results.

The rules are:

1) The list of names on both tabs can be of any length.
2) If a cell on the Names Tab does not have a number in the front do nothing and go on to the next cell.
3) If the cell has a number (of an undefined length) in front and cannot find same name from cell E2 to the end without the number prefix again do nothing and go on to the next cell.
4) If the cell has a number (of an undefined length) in front and can find same name from cell E2 to the end without the number prefix then remove the number from the front on that cell on the “Names” tab.

Here are some examples:

• Cell B5 on “Names” tab finds the same name in cell E4 on the “Data” tab so Cell B5 is converted to: Advertising and Promotion
• Cell B16 on “Names” tab finds the same name in cell E11 on the “Data” tab so Cell B16 is converted to: 2018YTD Credit Card Fees where the 4523 is removed.
• Cell B8 on “Names” tab has no number prefix, so the code does nothing to cell B8.

I hope all this makes sense. I am look forward to a nice clean solution. You people on this Forum are all great!!

Originally Posted by PCI

Try

Please Login or Register  to view this content.

Hi PCI, It appears to be working perfectly. Thanks you so much.

1)
Don't quote whole post.
If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding

For normal conversational replies, try using the QUICK REPLY box below.

2)
Try this

Please Login or Register  to view this content.

Hi PCI, That appears to work correctly now. I still need to do more testing before I edit this email to be marked "SOLVED". Thanks again!

An EZ way :

PHP Code: 

Sub Demo1()
     Dim VA, Rg As Range, V
         VA = Range("Data!E2").CurrentRegion.Value
    For Each Rg In Range("Names!B5").CurrentRegion
          If Rg.Value Like "#*" Then
              V = Application.Match(Mid(Rg.Value, InStr(Rg.Value, " ") + 1), VA, 0)
              If IsNumeric(V) Then Rg.Value = VA(V, 1)
          End If
    Next
End Sub 


Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

Hi Marc L, Your code appears to work as well. I will continue testing. Thanks