Looking for Formula to Extract Specific Text from a Cell

[hammerhead13]

I am looking to extract the SN: from the Cell Regardless of the other Info in the Cell. Is this Possible?

[TABLE="width: 500"]
<tbody>[TR]
[TD]Serial Number[/TD]
[TD]<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>Summary Description[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TSupport] Incident J3MH200000 routed to TEC SN: 8CG7242PFT Location: NYC[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][EXTERNAL] [TSupport] Incident J3IG115A84 routed to TEC SN: 8XG6203B3Q Location: NJ[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thank you in Advance!<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

 

[James006]

Hello,

With your data in cell B2 ...

=MID(B2,FIND("SN",B2)+4,FIND("Location",B2)-FIND("SN",B2)-5)

Hope this will help

 

[hammerhead13]

That works perfect. Thank you!

May I ask how I may be able to use it in a VLookup.

I have a Sheet and the Table is 'Connect Data Dump'!$A$2:$X$599. So basically I want it to be able to extract that serial from the cell in that dump essentially.

 

[James006]

You are welcome ...

If I understand correctly you need beforehand to use VLookup in order to retrieve the field ...

My recommendation would be to start by creating your VLookup formula ...

Once it performs as you need it to ... just replace B2 by your new Vlookup formula ...

Hope this will help

 

[hammerhead13]

I think I may be over-complicating this.

I previously was using this to find my information. The problem is that info changed and the length now varies so it would not pull the correct SN:

Currently using:
=IF(ISBLANK(A6)," ",MID(VLOOKUP(A6,'Connect Data Dump'!$B$2:$Y$599,8,FALSE),50,10))

Basically I am trying for something like this but I do not have the correct Syntax

=IF(ISBLANK(A4)," ",MID(VLOOKUP(A4,FIND("SN",H2)+4,FIND("Location",H2)-FIND("SN",B2)-5),'Connect Data Dump'!$A$2:$X$599,8,FALSE))

 

[James006]

May be ...

=IF(ISBLANK(A4)," ",MID(VLOOKUP(A4,FIND("SN",H2)+4,FIND("Location",H2)-FIND("SN",H2)-5),'Connect Data Dump'!$A$2:$X$599,8,FALSE))