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.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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:
Upvote 0
Hi,

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

Cheers,

Matty
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top