How to extract specific data from a cell

kripper

Board Regular
Joined
Dec 16, 2013
Messages
102
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.
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

kripper

Board Regular
Joined
Dec 16, 2013
Messages
102
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:

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,710
Hi,

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

Cheers,

Matty
 

kripper

Board Regular
Joined
Dec 16, 2013
Messages
102
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

Well-known Member
Joined
Feb 26, 2007
Messages
5,436
Office Version
2019
Platform
Windows
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

Board Regular
Joined
Dec 16, 2013
Messages
102
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

Well-known Member
Joined
Oct 24, 2015
Messages
5,075
your data is weird, many TABs, spaces, CRs and LFs
 

kripper

Board Regular
Joined
Dec 16, 2013
Messages
102
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

Well-known Member
Joined
Oct 24, 2015
Messages
5,075
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:
[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 :)
 
Last edited:

kripper

Board Regular
Joined
Dec 16, 2013
Messages
102
with PowerQuery aka Get&Transform:

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Data[/COLOR]
Smith, Jesse
Devine, Justice
Stark, Tony
Williams, Robin
***, Dumb
Again, Read
Smith, Wil
Baron, Red
Smith-Evans, Jesse

<tbody>
</tbody>


Code:
[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.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,999
Messages
5,447,803
Members
405,464
Latest member
Jayne125

This Week's Hot Topics

Top