Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Remove Redundant Characters (Excel 2003)

    Greetings All

    I used this formula to remove redundant characters from a Range
    =TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(A1,"(",""),")",""),",",""),"'",""),"-",""))
    it remove all except " from the cells. What is need to place in the formula to remove "

    Thanks

    regards,
    Attached Images Attached Images
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Remove Redundant Characters (Excel 2003)

    Try
    <code>
    =TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(A1,"""",""),"(",""),")",""), ",",""),"'",""),"-",""))
    </code>
    Note: this is a single formula even if your browser wraps the text.

  3. #3
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Remove Redundant Characters (Excel 2003)

    Another option :

    =MID(A1,ISNUMBER(FIND("(",A1))+1,SUMPRODUCT(--(CODE(RIGHT(A1,ROW(INDIRECT("1:"&LEN(A1)))))>64)))

    Regards
    Bosco

  4. #4
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Remove Redundant Characters (Excel 2003)

    appreciate if you can explain this interesting formula


    thanks, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Remove Redundant Characters (Excel 2003)

    Your formula doesn't handle non-alphabetic characters that are not at the end of a string. For example, (Power+Point) becomes Power+Poin, and "123 Go" becomes "1

  6. #6
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Remove Redundant Characters (Excel 2003)

    1] Good catch, thanks Hans

    2] Or try

    =SUBSTITUTE(LEFT(A1,LOOKUP(" ",T(INDIRECT(MID(A1,ROW($1:$255),1)&255)),ROW($1:$ 255))),"(","")

    or

    =SUBSTITUTE(LEFT(A1,LOOKUP(1,N(INDIRECT(MID(A1,ROW ($1:$255),1)&255)),ROW($1:$255))),"(","")



    Regard
    Bosco

Posting Permissions

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