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.
 
Try this

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:8pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:16.16px;" /><col style="width:16.16px;" /><col style="width:309.86px;" /><col style="width:98.85px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:20px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:78px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td > </td><td style="text-align:left; ">Date: 5/16/2019
<br />Employee  (Number): Smith, Jesse
<br />Call Back Date #1 : 5/16/2019
<br />Call Back Date #2 : 5/16/2019
<br />Call Back Date #3 : 5/16/2019
<br />Additional Follow-Up Required: NO
<br />Follow-Up Date #1 (if appicable): 5/16/2019
<br />Follow-Up Date #2 (if appicable): 5/16/2019
<br />Resolution Details:
<br /></td><td style="text-align:left; "> Smith, Jesse
<br /></td></tr><tr style="height:78px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td><td style="text-align:left; ">Date: 5/16/2019
<br />Employee  (Number):Devine, Justice
<br />Call Back Date #1 : 5/16/2019
<br />Call Back Date #2 : 5/16/2019
<br />Call Back Date #3 : 5/16/2019
<br />Additional Follow-Up Required: NO
<br />Follow-Up Date #1 (if appicable): 5/16/2019
<br />Follow-Up Date #2 (if appicable): 5/16/2019
<br />Resolution Details:
<br /></td><td style="text-align:left; ">Devine, Justice
<br /></td></tr><tr style="height:78px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td style="text-align:left; ">Date: 5/16/2019
<br />Employee  (Number):Stark, Tony<br />Call Back Date #1 : 5/16/2019
<br />Call Back Date #2 : 5/16/2019
<br />Call Back Date #3 : 5/16/2019
<br />Additional Follow-Up Required: NO
<br />Follow-Up Date #1 (if appicable): 5/16/2019
<br />Follow-Up Date #2 (if appicable): 5/16/2019
<br />Resolution Details:
<br /></td><td style="text-align:left; ">Stark, Tony<br /></td></tr><tr style="height:78px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td style="text-align:left; ">Date: 5/16/2019
<br />Employee  (Number):Williams, Robin<br />Call Back Date #1 : 5/16/2019
<br />Call Back Date #2 : 5/16/2019
<br />Call Back Date #3 : 5/16/2019
<br />Additional Follow-Up Required: NO
<br />Follow-Up Date #1 (if appicable): 5/16/2019
<br />Follow-Up Date #2 (if appicable): 5/16/2019
<br />Resolution Details:
<br /></td><td style="text-align:left; ">Williams, Robin<br /></td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D2</td><td >=TRIM(MID(SUBSTITUTE(SUBSTITUTE(C2,&#3 4;(Number):&#3 4;,REPT(&#3 4; &#3 4;,99)),&#3 4;Call&#3 4;,REPT(&#3 4; &#3 4;,99)),98,98))</td></tr></table></td></tr></table>
 
Upvote 0

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Something the editor did, this is the formula

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(C2,"(Number):",REPT(" ",99)),"Call",REPT(" ",99)),98,98))
 
Upvote 0
Just another formula if interested …

=MID(A1,FIND("):",A1)+2,FIND("Call",A1)-FIND("):",A1)-2)
 
Upvote 0
Something the editor did, this is the formula

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(C2,"(Number):",REPT(" ",99)),"Call",REPT(" ",99)),98,98))

I never had the chance to thank you for your assistance last time my friend.
Unfortunately life happened, and that sort of stopped me in my tracks for a bit.
Better now, and back at it....and your suggestion was exactly what I was looking for.
All the best good sir, and thank you so much for the assistance.
 
Upvote 0
I never had the chance to thank you for your assistance last time my friend.
Unfortunately life happened, and that sort of stopped me in my tracks for a bit.
Better now, and back at it....and your suggestion was exactly what I was looking for.
All the best good sir, and thank you so much for the assistance.

I'm glad to help you. I appreciate your kind comments.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
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