Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: How to extract specific data from a cell
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular kripper's Avatar
    Join Date
    Dec 2013
    Location
    Ontario, Canada
    Posts
    98
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How to extract specific data from a cell

    I am currently trying to figure out a way to extract a name from a cell with a different types of data.

    Specifically, the employee's name that will appear after the statement "Employee (NUMBER): " and the employee's name is obviously different lengths of characters.

    The current formula I am using is this
    Code:
    =MID(F171,SEARCH("Employee (NUMBER): ",F171)+38,15)
    , which is working for some of the names, however it misses characters in some, and pulls characters from the next line if the name is too short.

    As there are several lines within the cell, is there a way to have the formula recognize the carriage return or end of that line and not pick up and return data from the next line in the cell?

    Hoping I explained it correctly.

  2. #2
    Board Regular kripper's Avatar
    Join Date
    Dec 2013
    Location
    Ontario, Canada
    Posts
    98
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to extract specific data from a cell

    Was wondering since the employee's name is seperated by a comma between last name and first name, if I could just have the formula display all characters between the search results and the comma, i.e. last name and then index match from a helper column.

    Code:
    =INDEX(AGENTS[AGENT NAME],MATCH(MID(F169,SEARCH("Emploee Name):",F169)+37,25),AGENTS[LAST NAME],0))
    Last edited by kripper; May 19th, 2019 at 07:56 AM.

  3. #3
    Board Regular
    Join Date
    Feb 2007
    Location
    England
    Posts
    3,704
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to extract specific data from a cell

    Hi,

    Can you provide a few examples - including the desired results - so that we can test options?

    Cheers,

    Matty

  4. #4
    Board Regular kripper's Avatar
    Join Date
    Dec 2013
    Location
    Ontario, Canada
    Posts
    98
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to extract specific data from a cell

    Quote Originally Posted by Matty View Post
    Hi,

    Can you provide a few examples - including the desired results - so that we can test options?

    Cheers,

    Matty

    https://1drv.ms/x/s!Amk_7FQHP36kg85kT3cdSrGIHcQdkg

    Basically I only require the employee name to be extracted or even just the last name between end the search "Employee (Number):" and the comma, so I can index match the last name against another sheet to extract the agents full name. However it is currently providing the name and data from the next line in the cell.

  5. #5
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,944
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to extract specific data from a cell

    Your sample does not appear to be as you asked in the first post. What happened to the term "Employee." Suggest you repost with a representative sample of data--perhaps 4-8 instances. One instance does not provide enough opportunity to test.
    Let me know if that works for you
    Alan

    Am Yisrael Chai

    Win 10--Office 2019
    When Posting Code, please use code tags.
    How to insert Mcode to Power Query https://excel.solutions/2017/11/powe...te-code-video/


  6. #6
    Board Regular kripper's Avatar
    Join Date
    Dec 2013
    Location
    Ontario, Canada
    Posts
    98
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to extract specific data from a cell

    Quote Originally Posted by alansidman View Post
    Your sample does not appear to be as you asked in the first post. What happened to the term "Employee." Suggest you repost with a representative sample of data--perhaps 4-8 instances. One instance does not provide enough opportunity to test.
    Employee is still there, as originally posted Employee (NUMBER): is the search string and I am looking for the results after that is found.

    https://1drv.ms/x/s!Amk_7FQHP36kg85lghRSSvdE5f4PDQ

  7. #7
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,859
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How to extract specific data from a cell

    your data is weird, many TABs, spaces, CRs and LFs
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  8. #8
    Board Regular kripper's Avatar
    Join Date
    Dec 2013
    Location
    Ontario, Canada
    Posts
    98
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to extract specific data from a cell

    Quote Originally Posted by sandy666 View Post
    your data is weird, many TABs, spaces, CRs and LFs
    The data is imported from an espace alert in relation to my employees handling of customers, so it imports the entire interaction of the customer. What I am trying to do is identify which employee handled which call for reporting purposes.

  9. #9
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,859
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: How to extract specific data from a cell

    with PowerQuery aka Get&Transform:

    Data
    Smith, Jesse
    Devine, Justice
    Stark, Tony
    Williams, Robin
    ***, Dumb
    Again, Read
    Smith, Wil
    Baron, Red
    Smith-Evans, Jesse


    Code:
    // Table1
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ExtractAfter = Table.TransformColumns(Source, {{"Data", each Text.AfterDelimiter(_, ":", 1), type text}}),
        ExtractBefore = Table.TransformColumns(ExtractAfter, {{"Data", each Text.BeforeDelimiter(_, "Call"), type text}}),
        Clean = Table.TransformColumns(ExtractBefore,{{"Data", Text.Clean, type text}}),
        Trim = Table.TransformColumns(Clean,{{"Data", Text.Trim, type text}})
    in
        Trim
    edit:
    word a.s.s. is censored by forum
    Last edited by sandy666; May 19th, 2019 at 09:06 AM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  10. #10
    Board Regular kripper's Avatar
    Join Date
    Dec 2013
    Location
    Ontario, Canada
    Posts
    98
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to extract specific data from a cell

    Quote Originally Posted by sandy666 View Post
    with PowerQuery aka Get&Transform:

    [COLOR=#FFFFFF ]Data[/COLOR]
    Smith, Jesse
    Devine, Justice
    Stark, Tony
    Williams, Robin
    ***, Dumb
    Again, Read
    Smith, Wil
    Baron, Red
    Smith-Evans, Jesse


    Code:
    // Table1
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ExtractAfter = Table.TransformColumns(Source, {{"Data", each Text.AfterDelimiter(_, ":", 1), type text}}),
        ExtractBefore = Table.TransformColumns(ExtractAfter, {{"Data", each Text.BeforeDelimiter(_, "Call"), type text}}),
        Clean = Table.TransformColumns(ExtractBefore,{{"Data", Text.Clean, type text}}),
        Trim = Table.TransformColumns(Clean,{{"Data", Text.Trim, type text}})
    in
        Trim
    edit:
    word a.s.s. is censored by forum
    I am assuming VBA, unfortunately the systems at my work have disabled the VBA to stop malicious code from running.
    Thanks for the suggestion, wish I could use it as that is exactly what I am looking for, I just have to figure a way for a regular formula to do the same.

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
  •