Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: How to remove all characters after 17th in cell

  1. #1
    Board Regular
    Join Date
    Jan 2009
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How to remove all characters after 17th in cell

    Hello

    I have several rows of text data, the first 17 characters of which are in this format (4 letters_-_DD.MM.YYYY

    It's then followed by a variable number of characters i.e.

    ABCD - 01.02.2003 ABCDEFG HIJ KLMNO

    I would be grateful for a formula that will delete all characters to the right of the 17th character (i.e. the '3').

    Many thanks!

  2. #2
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    15,838
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    5 Thread(s)

    Default Re: How to remove all characters after 17th in cell

    If your text is in cell A1, put this in B1...

    =LEFT(A1,17)

    Copy this down column B

    If you want to then delete column A because it's not needed any more

    Select Column B and copy it
    Select from the menu Edit\ Paste Special: Values

    This will replace the formulas in column B with their results. You could now delete column A
    Last edited by AlphaFrog; Oct 20th, 2010 at 06:32 PM.

  3. #3
    Board Regular
    Join Date
    Jul 2009
    Location
    Lancashire, England
    Posts
    1,249
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to remove all characters after 17th in cell

    Hello

    Try.

    =REPLACE(A1,18,50,"")
    HTH
    Brian.

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

    Xl 2007 & 2010 Win 7

  4. #4
    Board Regular
    Join Date
    Jan 2009
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to remove all characters after 17th in cell

    That's fantastic, guys, many thanks, works a treat.

    Can I just ask you one more thing please?

    How can I identify and remove the last 4 characters from any cell containing file extensions .jpg .txt or .rtf? Not all cells contain these characters!

    Thanks once again.

  5. #5
    Board Regular
    Join Date
    Jul 2009
    Location
    Lancashire, England
    Posts
    1,249
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to remove all characters after 17th in cell

    Hello

    Try.

    =SUBSTITUTE(A2,LOOKUP(RIGHT(A2,4),{".jpg",".rtf",".txt"}),"")
    HTH
    Brian.

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

    Xl 2007 & 2010 Win 7

  6. #6
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    20,825
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to remove all characters after 17th in cell

    Perhaps use this formula

    =LEFT(A2,LEN(A2)-IF(OR(RIGHT(A2,4)={".jpg",".rtf",".txt"}),4,0))

  7. #7
    Board Regular
    Join Date
    Jan 2009
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to remove all characters after 17th in cell

    Meldoc/Barry - many thanks to you both, they both work fine.

Some videos you may like

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
  •