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

#### abrig005

##### Board Regular
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!

### Excel Facts

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

#### 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:
Excel Formula:
``=TRIM(RIGHT(SUBSTITUTE(A2,"-",REPT(" ",100)),100))``

#### abrig005

##### Board Regular
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

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)))``

#### abrig005

##### Board Regular

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.

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
Sure, I would be happy too.

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!

Replies
7
Views
159
Replies
5
Views
214
Replies
2
Views
170
Replies
0
Views
78
Replies
7
Views
60

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.

### Which adblocker are you using?

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

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