Replace a portion of a cell text if it contains a value in a list

[abrig005]

Hello,
Looking for help to do the following:
Column A has 50,000 rows with repetitive names (78 total) followed by a number.
I want to use Column H which contains a list of the prefixes in Column A and delete.
Flash Fill will not work because of some weird things in the data.
Text to columns will not work becasue - is present in some of the names in Column A.
Any ideas?
Thank you!

 

[Joe4]

It looks like you just want the number after the last "-".
If that is the case, then just put this formula in cell B2 and copy down for all rows:

=TRIM(RIGHT(SUBSTITUTE(A2,"-",REPT(" ",100)),100))

 

[abrig005]

Thanks, however in some cases there are more than on - which then cuts off the value. For example, Anthony - 1569 - 8988 becomes 8988 not 1569 - 8988

It looks like you just want the number after the last "-".
If that is the case, then just put this formula in cell B2 and copy down for all rows:

=TRIM(RIGHT(SUBSTITUTE(A2,"-",REPT(" ",100)),100))

 

[Joe4]

Thanks, however in some cases there are more than on - which then cuts off the value. For example, Anthony - 1569 - 8988 becomes 8988 not 1569 - 8988

OK, that part was not clear. When you said that there could be more than one, I was assuming that you meant in the name, i.e. "Marc-Jean".
It is usually wise to include the different possibilites in the samples you post, so we can clearly see what you are working with.

Provided the extra "-' will always be in the number and not in the name, then try this instead:

=TRIM(MID(A2,FIND("-",A2)+1,LEN(A2)))

 

[abrig005]

Thank you! It seems to work well! If you have time can you possibly explain what you did so I know how it works? If not its fine I appreciate your time!

=TRIM(MID(A2,FIND("-",A2)+1,LEN(A2)))

 

[Joe4]

Thank you! It seems to work well! If you have time can you possibly explain what you did so I know how it works? If not its fine I appreciate your time!

Sure, I would be happy too.

Let's start with this part here:
FIND("-",A2)
This is using the FIND function to locate where the first incidence of "-" occurs in the string in cell A2.

Then we use the MID function, telling it to start one space after where it finds the first "-" in the string:
MID(A2,FIND("-",A2)+1,LEN(A2))
For the third argument of the MID function (the total length), it doesn't really matter how long we tell it, as long as we make sure it gets to at least the end of the string. So LEN(A2) will get us there (and then some). Sometimes you see people pick some large number, like "100".

Lastly, we just wrap the whole thing in the TRIM function, which trims off any leading and trailing spaces (i.e. if there is a space after the "-").
=TRIM(MID(A2,FIND("-",A2)+1,LEN(A2)))

Hope that all makes sense!

 

[abrig005]

Sure, I would be happy too.

Let's start with this part here:
FIND("-",A2)
This is using the FIND function to locate where the first incidence of "-" occurs in the string in cell A2.

Then we use the MID function, telling it to start one space after where it finds the first "-" in the string:
MID(A2,FIND("-",A2)+1,LEN(A2))
For the third argument of the MID function (the total length), it doesn't really matter how long we tell it, as long as we make sure it gets to at least the end of the string. So LEN(A2) will get us there (and then some). Sometimes you see people pick some large number, like "100".

Lastly, we just wrap the whole thing in the TRIM function, which trims off any leading and trailing spaces (i.e. if there is a space after the "-").
=TRIM(MID(A2,FIND("-",A2)+1,LEN(A2)))

Hope that all makes sense!

It makes sense, thank you again!