Results 1 to 8 of 8

Thread: Extracting Piece Of Text Having Specific Pattern From A Larger Text String

  1. #1
    Junior Member
    Join Date
    Aug 2013
    Posts
    5
    Rep Power
    0

    Extracting Piece Of Text Having Specific Pattern From A Larger Text String

    Quote Originally Posted by Excel Fox View Post
    0 is added just to ensure that there are no errors. Everything else can be found out by evaluating the formula. Post back if that's still not clear.
    Hi,
    I liked your thread but I have a different query.

    I have the following data in excel:

    Column A Column B
    Safe and sound A0001199 Safe
    London Cars S0100999 LC
    Wembley Motors Q0001988 We
    And so on…

    In column B, I need to extract A0001199 (in cell B1), S0100999 (in cell B2) and Q0001988 (in cell B3).

    Please could you help, how I can extract those data from column A to column B?

    Thank you in advance
    Firoz

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    hi nomanfiroz, please start your own thread with specific and meaningful thread titles, unless it is exactly what you are looking for (in which case the solution would have already been provided), and do not hijack another thread. If the context of the thread is in line with your query, you can post a link of that thread in your new thread. I have moved your post to a new thread this time.

    coming to your query, can you confirm that the text that you want to extract follows a specific pattern, ie, one characters followed by 7 numbers, or in pattern languange, ?#######
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Assuming my assumption above is right, here's a classic lazy one. You could find many better variants around if you searched well enough though.

    =MID(SUBSTITUTE(TRIM(A1)," ","|",LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))-1),FIND("|",SUBSTITUTE(TRIM(A1)," ","|",LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))-1))+1,8)

    If I were to add another assumption that the first numeric character in the entire text string will be the one immediately after the first character of the text patter you want to extract, then this will also work

    =MID(A1,SUMPRODUCT(MIN(IFERROR(FIND(ROW(OFFSET($A$ 1,,,10,))-1,A1),FALSE)))-1,8)
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  4. #4
    Junior Member
    Join Date
    Aug 2013
    Posts
    5
    Rep Power
    0
    Dear Administrator,

    Thank you very much for your reply.

    When I enter the formula [=MID(A1,SUMPRODUCT(MIN(IFERROR(FIND(ROW(OFFSET($A$ 1,,,10,))-1,A1),FALSE)))-1,8) ], sometimes it picks the data and sometime it does not. For example:

    Column A Column B Column C
    Narratio Acc no Comment

    BARFRD H LTD Z0001670 Z0001670 Picked
    SJ&EM GRIGG S J GRIGG E3004951 3004951 Not picked
    ETW GAR T/AS SW E3000055 FP 27/08/13 1039 RP4679963388259900 E3000055 3000055 Not picked
    RAMORA LIMITED E0011232 FP 24/08/13 0505 53050526501454000N E0011232 E0011232 Picked
    SUITOR MOTORS SUITORS N0001147 N0001147 Picked
    MOTORSPORTL N4007070 4007070 Not picked
    SAGEPARTS GSE LTD B230813 30813 Not picked

    Often any account number beginning with E does not pick. I am sorry I do not understand or know how to fix it. Please could you help. Your help and support will be highly appreciated.

    Many thanks in advane and kind regards,
    Firoz

  5. #5
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    Based on your examples:

    =LEFT(MID(A1,FIND("0",A1)-1,100),FIND(" ",MID(A1,FIND("0",A1)-1,100)&" "))

  6. #6
    Junior Member
    Join Date
    Aug 2013
    Posts
    5
    Rep Power
    0
    Dear SNB

    Sorry, it gave me the exact same result as follows:

    I have data in column A, result in column B and comment (Optional) in column C

    BARFRD H LTD Z0001670 Result Z0001670 Comment Picked
    SJ&EM GRIGG S J GRIGG E3004951 Result 3004951 Comment Not picked
    ETW GAR T/AS SW E3000055 FP 27/08/13 1039 RP4679963388259900 E3000055 Result 3000055 Comment Not picked
    RAMORA LIMITED E0011232 FP 24/08/13 0505 53050526501454000N E0011232 Result E0011232 Comment Picked
    SUITOR MOTORS SUITORS N0001147 Result N0001147 Comment Picked
    MOTORSPORTL N4007070 Result 4007070 Comment Not picked
    SAGEPARTS GSE LTD B230813 Result 30813 Comment Not picked

    Sometime it did pick, sometime it did not. We need that 8 digits account number (Like as Z0001670) to compare two sets of data. Please could you help?

    Many thanks in advance
    Firoz

  7. #7
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Give this a try.

    =MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456 789"))-1,8)
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  8. #8
    Junior Member
    Join Date
    Aug 2013
    Posts
    5
    Rep Power
    0
    It worked absolutely fine.
    Thank you so much.

    I have saved 24 hours working time today.

    Thank you very much and kind regards,
    Firoz

Similar Threads

  1. Replies: 6
    Last Post: 06-01-2013, 03:24 PM
  2. Replies: 14
    Last Post: 05-25-2013, 06:55 AM
  3. Extracting Numeric Values From Alphanumeric Text
    By Safal Shrestha in forum Excel Help
    Replies: 3
    Last Post: 03-21-2013, 12:04 PM
  4. Find a text substring that matches a given "pattern"
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 2
    Last Post: 02-10-2013, 06:19 AM
  5. Find the First or Last So Many Words in a Text String
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 6
    Last Post: 06-21-2012, 09:42 PM

Posting Permissions

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