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

Thread: Extract Text

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

    Default Extract Text

    Hi,

    I need to extract Text from a cell to different columns.

    Eg : a cell is containing below text
    • S/N: IN17001671 BATCH:JAN/17 IN13022537 Batch Mar 17 , IN13022385; IN13022378


    I need the TEXT starting IN1 in different columns. (Character Length : 10)

    Here my output in different columns is as below
    Text Column 1 Column 2 Column 3 Column 4
    S/N: IN17001671 BATCH:JAN/17 IN13022537 Batch Mar 17 , IN13022385; IN13022378 IN17001671 IN13022537 IN13022385 IN13022378

    Help me to make a formulae for this.

    Thanks in advance.

    B.Srinivasa Rao

  2. #2
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    5,638
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Extract Text

    Quote Originally Posted by seenai View Post
    I need the TEXT starting IN1 in different columns. (Character Length : 10)
    Hi, here is one option, formula can be copied down and across as required.

    Excel 2013/2016
    ABCDEFGHI
    2S/N: IN17001671 BATCH:JAN/17 IN13022537 Batch Mar 17 , IN13022385; IN13022378 IN17001671 IN13022537 IN13022385 IN13022378 IN17001671IN13022537IN13022385IN13022378IN17001671IN13022537IN13022385IN13022378

    Sheet1



    Worksheet Formulas
    CellFormula
    B2=IFERROR(MID($A2,FIND("|",SUBSTITUTE($A2,"IN1","|",COLUMNS($B2:B2))),10),"")

    [code]your code[/code]

  3. #3
    New Member
    Join Date
    Mar 2013
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Text

    EXCELLENT. It works exactly.

    Thanks for your Quick help.

  4. #4
    New Member
    Join Date
    Mar 2013
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Text

    Hi,

    I need to exclude Test starting with MIN1. Please help to update the formula. Eg :MIN13022385 not to be considered.

    Text Column 1 Column 2 Column 3
    S/N:IN17001671BATCH:JAN/17 IN13022537Batch Mar 17 ,MIN13022385; IN13022378 IN17001671 IN13022537 IN13022378

  5. #5
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    5,638
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Extract Text

    Quote Originally Posted by seenai View Post
    need to exclude Test starting with MIN1.
    Hi, here is one option:

    Excel 2013/2016
    ABCD
    1TextColumn 1Column 2Column 3
    2S/N:IN17001671BATCH:JAN/17 IN13022537Batch Mar 17 ,MIN13022385; IN13022378IN17001671IN13022537IN13022378

    Sheet1



    Worksheet Formulas
    CellFormula
    B2=IFERROR(MID($A2,FIND("|",SUBSTITUTE(SUBSTITUTE($A2,"MIN","XXX"),"IN1","|",COLUMNS($B2:B2))),10),"")

    [code]your code[/code]

Some videos you may like

User Tag List

Tags for this Thread

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
  •