Results 1 to 9 of 9

Copy & remove text before & after specific character

This is a discussion on Copy & remove text before & after specific character within the Excel Questions forums, part of the Question Forums category; How do I move to another column, all text from a cell that follows a specific character, such as ":"? ...

  1. #1
    Board Regular
    Join Date
    Aug 2004
    Location
    United States
    Posts
    145

    Default Copy & remove text before & after specific character

    How do I move to another column, all text from a cell that follows a specific character, such as ":"?

    Also, How do I move to another column, all text from a cell that precedes a specific character, such as ">"?

  2. #2
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,499

    Default Re: Copy & remove text before & after specific character

    Try like this

    Sheet14

     AB
    1Fred:ScuttleScuttle
    2Fred>ScuttleFred

    Spreadsheet Formulas
    CellFormula
    B1=RIGHT(A1,LEN(A1)-FIND(":",A1))
    B2=LEFT(A2,FIND(">",A2)-1)


    Excel tables to the web >> Excel Jeanie HTML 4
    HTH, Peter
    Please test any code on a copy of your workbook.

  3. #3
    Board Regular
    Join Date
    Aug 2004
    Location
    United States
    Posts
    145

    Default Re: Copy & remove text before & after specific character

    Thanks.

    How do I at the same time remove from the original cell what was moved to the other column?

  4. #4
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,499

    Default Re: Copy & remove text before & after specific character

    Try like this

    Sheet14

     ABC
    1Fred:ScuttleScuttleFred:
    2Fred>ScuttleFred>Scuttle

    Spreadsheet Formulas
    CellFormula
    B1=RIGHT(A1,LEN(A1)-FIND(":",A1))
    C1=SUBSTITUTE(A1,B1,"")
    B2=LEFT(A2,FIND(">",A2)-1)
    C2=SUBSTITUTE(A2,B2,"")


    Excel tables to the web >> Excel Jeanie HTML 4
    HTH, Peter
    Please test any code on a copy of your workbook.

  5. #5
    Board Regular
    Join Date
    Aug 2004
    Location
    United States
    Posts
    145

    Default Re: Copy & remove text before & after specific character

    Fantastic.

    How would I move text to another column by finding the word "yield" and not only moving the word "yield" but several characters before it?

    Such as "20,000 yield" or "(5,000 yield)" or "1500 yield".
    The amounts change but not the word.

    I don't want the parenthesis just for example "5,000 yield".

  6. #6
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,499

    Default Re: Copy & remove text before & after specific character

    Maybe

    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"yield",""),"(",""),")","")+0
    HTH, Peter
    Please test any code on a copy of your workbook.

  7. #7
    Board Regular
    Join Date
    Aug 2004
    Location
    United States
    Posts
    145

    Default Re: Copy & remove text before & after specific character

    Sorry, that didn't work. Got a "Value" error

  8. #8
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,499

    Default Re: Copy & remove text before & after specific character

    Give some examples of existing values.
    HTH, Peter
    Please test any code on a copy of your workbook.

  9. #9
    Board Regular
    Join Date
    Aug 2004
    Location
    United States
    Posts
    145

    Default Re: Copy & remove text before & after specific character

    Drum (25 000 Yield)
    Drum (30 000 Yield)
    Drum (25 000 Yield)
    Drum (40 000 Yield)
    Drum (Yield 30 000)
    Black Toner 10 000 Yield
    Cyan Toner 6 600 Yield
    Magenta Toner 6 600 Yield
    Yellow Toner 6 600 Yield

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