Data clean up w/ SUBSTITUTE - Page 2
Manage your personal finances in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 12 of 12

Thread: Data clean up w/ SUBSTITUTE

  1. #11
    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

     
    [quote]
    On 2002-02-19 14:39, IML wrote:
    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.

    [quote]

    I devised this formula (of which an extended description how it works is available in the Archives) to remove the initial or closing nums from a string: aky1234 --> aky or the inverse operation: aky1234 --> 1234. The case to which I'm now applying it is a bit special. UPPER'ing is only needed, it seems, if you also have cases like Denise Dukakof, PHD. I advised expanding the constant array itself with such cases:

    {"Jr.",", POD",", PhD.","JR.",", POD",", PHD"}

    Both tactics should work, but maybe the latter is safer (untested).

    Aladin

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

    Default

      
    sorry for resisting...works quite well. quite smart of you. needs a bit of tweeking to fit exactly what i need, but for working blindly, pretty impressive.

    Thanks

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