Data clean up w/ SUBSTITUTE
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Data clean up w/ SUBSTITUTE

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Austin, TX
    Posts
    202
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,581
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    202
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    can you post a sample of your data?

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Austin, TX
    Posts
    202
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Austin, TX
    Posts
    202
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,772
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    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

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Austin, TX
    Posts
    202
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  8. #8
    Board Regular RichardS's Avatar
    Join Date
    Feb 2002
    Location
    Victoria, Australia
    Posts
    761
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,772
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    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


  10. #10
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com