+ Reply to Thread
Results 1 to 10 of 10

Extract from body of text

  1. #1
    Registered User
    Join Date
    01-07-2013
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    3

    Extract from body of text

    Hey guys,

    I've just really started to use excel, and I'm getting a bit frustrated here.

    Here is some sample text:

    Number: Go 123 fast
    Number: Running apps
    Phrase: Testing 12345

    I've been trying to use MID and FIND functions to take the output from operation into one cell, output of task in another, etc. For example all text after the colon for operation, and before the next work "task:". The contents of these always change though. Is MID and FIND the right way to do this? Thanks.
    Last edited by lekwk; 01-08-2013 at 05:54 PM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Extract from body of text

    without some examples of your expected outcome, its a little hard 5to make out what you want, but give this a try and maybe it will help get you in the right direction?

    =MID(A1,SEARCH(":",A1,1)+2,99)

    If this is not what you want, you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: Extract from body of text

    Hi lekwk,

    Is the sample text that you provided all in 1 cell? Or separate cells?

  4. #4
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Extract from body of text

    to enhance the mid function if the test is in one cell and separated by char (10)

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  5. #5
    Registered User
    Join Date
    01-07-2013
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Extract from body of text

    Thanks for your responses.

    Quote Originally Posted by djapigo View Post
    Hi lekwk,

    Is the sample text that you provided all in 1 cell? Or separate cells?
    Yes, unfortunately it's exported within 1 cell.
    Last edited by lekwk; 01-08-2013 at 05:52 PM.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Extract from body of text

    will the data always be in that sequence?
    I presume there will be a lot more than 1 "set" of data?

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Extract from body of text

    for the "task" section, try this....
    =MID(A8,SEARCH("Task",A8,1)+6,(SEARCH("Error",A8,1)-2)-(SEARCH("task",A8,1)+6))

    Im working on the others

    edit: ok this is turning into brute force, maybe some-one can find a more elegant way...
    for the "error message" use this...
    =MID(A8,SEARCH("message",A8,1)+9,(SEARCH(":",A8,SEARCH("message",A8,1)+10)-6)-(SEARCH(":",A8,SEARCH("message",A8,1))))
    and for the last part...
    =RIGHT(A8,LEN(A8)-(SEARCH(":",A8,SEARCH("message",A8,1)+10)+2))
    Note, you have trailing blanks spaces on that text
    Last edited by FDibbins; 01-08-2013 at 01:04 AM.

  8. #8
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Extract from body of text

    can you try this in cell E3 then fill to the right..

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  9. #9
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211

    Re: Extract from body of text

    E3: =IFERROR(TRIM(LEFT(SUBSTITUTE(MID($A8,SEARCH(E2,$A8)+LEN(E2)+2,99),CHAR(10),REPT(" ",99)),99)),"")

    Copy across
    Last edited by Teethless mama; 01-08-2013 at 02:12 AM.

  10. #10
    Registered User
    Join Date
    01-07-2013
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    3

    Wink Re: Extract from body of text

    My god...

    FDBibbins, vlady, mama, thank you all!!!!!!

    This is perfect. Just made an adjustment in red to make that cell reference an absolute:

    =IFERROR(MID($A8, SEARCH($E$2,$A8)+LEN($E$2)+1, SEARCH(CHAR(10),$A8,SEARCH($E$2,$A8))-SEARCH($E$2,$A8)-LEN($E$2)),"N/A")

    Need to drag vertically.

    Thanks again!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1