Copy & remove text before & after specific character

Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: Copy & remove text before & after specific character

  1. #1
    Board Regular
    Join Date
    Aug 2004
    Location
    United States
    Posts
    158
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    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
    158
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    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
    158
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    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
    158
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

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

  8. #8
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    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
    158
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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

  10. #10
    New Member
    Join Date
    Mar 2015
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

      
    Very precious commands.

    How to do B2 if there is more than 2 words and multiple ":". For example Fred:Scuttle:Paris and I want to have only the last word? Right now it would give "Scuttle:Paris"

    Thanks


    Quote Originally Posted by VoG View Post
    Try like this

    Sheet14

    * A B
    1 Fred:Scuttle Scuttle
    2 Fred>Scuttle Fred

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


    Excel tables to the web >> Excel Jeanie HTML 4

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