How to extract specific data from a cell

[kripper]

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

=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.

 

[kripper]

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.

=INDEX(AGENTS[AGENT NAME],MATCH(MID(F169,SEARCH("Emploee Name):",F169)+37,25),AGENTS[LAST NAME],0))

 

[Matty]

Hi,

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

Cheers,

Matty

 

[kripper]

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.

 

[alansidman]

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.

 

[kripper]

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

 

[sandy666]

your data is weird, many TABs, spaces, CRs and LFs

 

[kripper]

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.

 

[sandy666]

with PowerQuery aka Get&Transform:

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#F79646]Data[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FDE9D9]Smith, Jesse[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Devine, Justice[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FDE9D9]Stark, Tony[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Williams, Robin[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FDE9D9]***, Dumb[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Again, Read[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FDE9D9]Smith, Wil[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Baron, Red[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FDE9D9]Smith-Evans, Jesse[/td][/tr]
[/table]

[SIZE=1]// 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[/SIZE]

edit:
word a.s.s. is censored by forum

 

[kripper]

with PowerQuery aka Get&Transform:

[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #F79646"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Data[/COLOR][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FDE9D9]#FDE9D9[/URL] "]Smith, Jesse[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]Devine, Justice[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FDE9D9]#FDE9D9[/URL] "]Stark, Tony[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]Williams, Robin[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FDE9D9]#FDE9D9[/URL] "]***, Dumb[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]Again, Read[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FDE9D9]#FDE9D9[/URL] "]Smith, Wil[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]Baron, Red[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FDE9D9]#FDE9D9[/URL] "]Smith-Evans, Jesse[/TD]
[/TR]
</tbody>[/TABLE]

[SIZE=1]// 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[/SIZE]

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.