Data clean up w/ SUBSTITUTE

msvec

Board Regular
Joined
Feb 18, 2002
Messages
202
I'm trying to remove multiple strings from a column of data, and was hoping to do this with one function in an adjacent column. I've exhausted trying arrays and circumventing nesting...any clue how to do this without using VB.

Thanks much
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Example data would be really helpful. Also--do you need to do with a formula? Have you tried find/replace?
 
Upvote 0
Sample data, OK, in column A, would be the following: Mike Smith Jr., Ted Jones, PhD., Mr. & Mrs. Tom Black, POD

If this is possible, the formula in col B would return: Mike Smith, Ted Jones, Mr. & Mrs. Tom Black

simple, i know, but i'm trying to replace the Jr, PhD, POD, etc. The same strings will always have to be replaced. Find/replace would have to be run too many times. i can do this in VB, but i'm trying to keep it simple for this client.

thanks for your help
 
Upvote 0
Aladin, I had come up with something similar to your efforst to isolate text after the last space, as follows:

=RIGHT(TRIM(A1),LEN(TRIM(A1))-FIND("^",SUBSTITUTE(TRIM(A1)," ","^",LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ","")))))

I suppose i can use this in conjunction with a lookup up to determine if the last piece of text needs to go, but i was hoping to use a substitute function maybe with an array to replace all listed strings with "".

any body else with some help. again, this is something that needs to be done repetitevly, and with confidential data, hence my client's insistence that he do it via formulas.

thanks
 
Upvote 0
OK, here we go. What follows is an essential part of the formulas under the link I mentioned. Apparently, you need a shorter version.

=TRIM(SUBSTITUTE(A1,RIGHT(A1,SUMPRODUCT((LEN(A1)-LEN(SUBSTITUTE(A1,{"Jr.",", POD",", PhD."},""))))),""))

You need to expand the {"Jr.",", POD",", PhD."} bit to remove other undesired substrings.

Aladin
 
Upvote 0
This is very similar to my first attempt at this. Problem is that arrays don't work in a SUBSTITUTE function/formula...only the first value in the array does, which makes sense since it has an 'instance' criteria, although when omitted represent all.

BUT, let me know if I'm wrong or missing something. What i've resorted to is to using my original formula against an OR array containing the strings i want out. if it matches, pull the 2nd to last text enclosed by spaces, else, my formula.

any thoughts.
 
Upvote 0
Just a quick querie on the data. Does a comma separate the name you want from the superfluous titles in every case? If so, couldn't you use find to determine where the comma is, then left to extract data to the left of the comma?
Richard
 
Upvote 0
On 2002-02-19 14:27, msvec wrote:
This is very similar to my first attempt at this. Problem is that arrays don't work in a SUBSTITUTE function/formula...only the first value in the array does, which makes sense since it has an 'instance' criteria, although when omitted represent all.

BUT, let me know if I'm wrong or missing something. What i've resorted to is to using my original formula against an OR array containing the strings i want out. if it matches, pull the 2nd to last text enclosed by spaces, else, my formula.

any thoughts.

Just try the formula to see that it works.

It transforms your sample

{"Mike Smith Jr.";"Ted Jones, PhD.";"Mr. & Mrs. Tom Black, POD"}

into

{"Mike Smith";"Ted Jones";"Mr. & Mrs. Tom Black"}

Aladin
 
Upvote 0
I tried this and couldn't get it to work. To prevent user error such as mine with capitilzation, you could tweak this everso slightly with
=TRIM(SUBSTITUTE(A1,RIGHT(A1,SUMPRODUCT((LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),{"JR.",", POD",", PHD."},""))))),""))

very nice, Aladin.

On 2002-02-19 14:10, Aladin Akyurek wrote:
OK, here we go. What follows is an essential part of the formulas under the link I mentioned. Apparently, you need a shorter version.

=TRIM(SUBSTITUTE(A1,RIGHT(A1,SUMPRODUCT((LEN(A1)-LEN(SUBSTITUTE(A1,{"Jr.",", POD",", PhD."},""))))),""))

You need to expand the {"Jr.",", POD",", PhD."} bit to remove other undesired substrings.

Aladin
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

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
Back
Top