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

abrig005

Board Regular
Joined
Jan 6, 2017
Messages
69
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!
tableauex.JPG
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,031
Office Version
  1. 365
Platform
  1. Windows
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:
Excel Formula:
=TRIM(RIGHT(SUBSTITUTE(A2,"-",REPT(" ",100)),100))
 

abrig005

Board Regular
Joined
Jan 6, 2017
Messages
69
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:
Excel Formula:
=TRIM(RIGHT(SUBSTITUTE(A2,"-",REPT(" ",100)),100))
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,031
Office Version
  1. 365
Platform
  1. Windows
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:
Excel Formula:
=TRIM(MID(A2,FIND("-",A2)+1,LEN(A2)))
 
Solution

abrig005

Board Regular
Joined
Jan 6, 2017
Messages
69

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,031
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jan 6, 2017
Messages
69
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!
 

Forum statistics

Threads
1,147,517
Messages
5,741,629
Members
423,674
Latest member
Charles2dodo

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
Top