Extract Text After Specific Keyword

    Hi,
    I know the very basics of excel, so be gentle with me here... (also, this is my first thread, and I did searched for similar threads, couldn't find any) so here it is


    I have a sheet with only one colomn, and about 200 rows, containing alot of 'garbage'. Each few rows, I have a keyword ("REFDES=") and after this keyword I have a value ("R3254"). I need to extract only these values and to put them in one row, few colomns.
    For example, if the sheet looks like this:


    A 1320 1539 16 3 3 0 POWER=0.05W
    A 1368 1539 16 3 3 0 INFO=FILE://NT14/APPLIC/INFOLINKS/34759211M.BAT
    A 1441 1505 16 0 4 3 REFDES=R3099
    A 1224 1539 16 3 3 0 FR=-
    A 1336 1539 16 3 3 0 PKG_TYPE=RES0402
    A 1550 1150 16 0 3 0 POWER=0.063W
    A 1550 1198 16 0 3 0 INFO=FILE://NT14/APPLIC/INFOLINKS/00009319N.BAT
    A 1550 1054 16 0 3 0 FR=-
    A 1586 1281 16 0 4 3 REFDES=U097
    A 1550 1166 16 0 3 0 PKG_TYPE=RES0402
    A 1550 1102 16 0 3 0 PRICE=0.002


    Then the output will look like this:
    R3099 U097
    (in 2 colomns).
    The number of colomns should be around 12...
    just for the curious ones, this file is actually a text representation of an electrical circuit.


    I tried doing it myself (with MACRO), but failed.
    I need it really urgent (my boss will fire me if not...) so ANY help would be really really great.
    Thanks all!
    Roy

    Re: Extract A Text After A Specific Keyword


    Don't know the macro way, but you could do =IF(ISERROR(FIND(A1,"REFDES")),"",Right(A1,Len(A1)-FIND(A1,"="))). That will kick out the value you need in B. After that, you could use filters to not show blanks in B, highlight it, click Ctrl+;, copy and Paste Special (selecting Transpose and Values) where you need the info to be.

    Re: Extract A Text After A Specific Keyword


    Use this formula
    =SUBSTITUTE(IF(ISERROR(FIND("REFDES",A1)),"",MID(A1,FIND("REFDES",A1),255)),"="," ")


    Edit:


    Mis read your post, working on VBA solution

    [FONT="Arial Black"][COLOR="blue"][SIZE="4"]Bill[/SIZE][/COLOR][/FONT]
    Tip: To avoid chasing code always use Option Explicit.

    Re: Extract A Text After A Specific Keyword


    Ok, thanks for the quick reply, just a question-what do you mean by:
    That will kick out the value you need in B.


    What's 'B' mean?
    ALl the data is stored in colomn A.
    Thanks,
    Roy[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Bill,
    a general quesion- where do I put this formula?
    In one of the cells?
    just as simple as that? cause I did it, but nothing happend...
    when you wrote A1 - it means only the first cell, right? but I need the whole colomn.
    Maybe I'm not getting it right... sorry...
    Roy

    Re: Extract A Text After A Specific Keyword


    I assumed the column of data you had is in column A and the formula I suggested was in column B. The cell would be blank if REFDES is not found, but would have R3099 or U079, etc., if it was. Bill's formula also works, but requires you to take the same steps after you've used it to get the data into one row.


    For both of our formulas, you will have to drag the formula down the length of the array. Highlight the cell, move the mouse to the lower right-hand corner of it until it becomes a black cross, then click, hold, and drag down as far as necessary.

    Re: Extract A Text After A Specific Keyword


    Ok, Just did it and it works!
    Thank you very much guys!!!
    You have just save my carreer....! (kidding about this one he-he-he)
    But really, thanks alot!!!!
    Roy

    Re: Extract A Text After A Specific Keyword


    If your input is in column A this will output to columns B,C,D ......


    [FONT="Arial Black"][COLOR="blue"][SIZE="4"]Bill[/SIZE][/COLOR][/FONT]
    Tip: To avoid chasing code always use Option Explicit.

    Re: Extract Text After Specific Keyword


    Your Welcome Roy, Gald to help. Tell your friends about OzGrid.

    [FONT="Arial Black"][COLOR="blue"][SIZE="4"]Bill[/SIZE][/COLOR][/FONT]
    Tip: To avoid chasing code always use Option Explicit.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login