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.

 

[DanteAmor]

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>

 

[DanteAmor]

Something the editor did, this is the formula

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

 

[sandy666]

I am assuming VBA, unfortunately the systems at my work have disabled the VBA to stop malicious code from running.

Very good rule

I just have to figure a way for a regular formula to do the same.

Sure, no problem

Have a nice day

 

[mse330]

Just another formula if interested …

=MID(A1,FIND("):",A1)+2,FIND("Call",A1)-FIND("):",A1)-2)

 

[kripper]

Something the editor did, this is the formula

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

Thanks my friend, it worked!

 

[kripper]

Just another formula if interested …

=MID(A1,FIND("):",A1)+2,FIND("Call",A1)-FIND("):",A1)-2)

Thank you so much....that did exactly what I was hoping for. Greatly appreciate it.

 

[mse330]

You are most welcome & thanks for the feedback

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[kripper]

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.

 

[DanteAmor]

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.