Data clean up w/ SUBSTITUTE

[msvec]

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

[Anne Troy]

Example data would be really helpful. Also--do you need to do with a formula? Have you tried find/replace?

[anno]

can you post a sample of your data?

[msvec]

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

[msvec]

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

[Aladin Akyurek]

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

[msvec]

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.

[RichardS]

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

[Aladin Akyurek]

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

[IML]

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