Extract Specific Data within text cell

[psyracusa]

Hi - I have a comments text cell and somewhere within will contain a specific string of numbers I need to extract and place into a seperate cell.
The number I need to look for will always be in a ###-###-### format (ex: 145-875-886)
How do have excel scan the cell for this format and automatically extract it to a new cell for me?
Many thanks.

 

[L. Howard]

How about posting about four or five examples of the comments that would represent typical cell contents.

Howard

 

[hiker95]

psyracusa,

Welcome to the MrExcel forum.

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?


To start off, and, so that we can get it right on the first try:

Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post a small screen shot (NOT a graphic, or, picture, or, flat text) try one of the following:

MrExcel HTML Maker
If you do not know how to install and how to use HTML Mr.Excel Maker
https://www.youtube.com/watch?v=JycvgGppxt0&feature=youtu.be

Excel Jeanie
Download

Borders-Copy-Paste
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

To test the above:
Test Here


Or, you can upload your workbook to (the BLUE link-->) Box Net ,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[psyracusa]

Typical cell will look like:
[TABLE="width: 925"]
<tbody>[TR]
[TD]Cell K1 = send customer terminal sn 168-179-329 reg3 ip: 010.169.101 gateway: 010.101.001 -- JNQ-1501920[/TD]
[/TR]
[TR]
[TD]Cell K2 = MCM Readiness - Register 3 S/N 168-179-445 Not Showing Correctly in VHQ, Maxwell Ticket #SHK-1309 pending update[/TD]
[/TR]
[TR]
[TD]Cell K106 = Send Customer Terminal S/N 168-179-655[/TD]
[/TR]
[TR]
[TD]Cell K325 = Send CT S/N 168-179-673 register 1 Gateway 0187.049.001 IP 010.0187.049

I need to extract the S/N # (sn) and it's always ###-###-### that I bolded above and paste it into the adjacent next column (L)
Thank you.[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

 

[István Hirsch]

Put this formula in cell L1 and copy down:

=TRIM(MID(K1,MIN(SEARCH({" sn????-???-????"," S/N????-???-????"},K1&" sn ???-???-??? S/N ???-???-??? "))+4,12))

Excel Workbook
	A	B
1	send customer terminal sn 168-179-329 reg3 ip: 010.169.101 gateway: 010.101.001 -- JNQ-1501920	168-179-329
2	MCM Readiness - Register 3 S/N 168-179-445 Not Showing Correctly in VHQ, Maxwell Ticket #SHK-1309 pending update	168-179-445
3	Send Customer Terminal S/N 168-179-655	168-179-655
4	Send CT S/N 168-179-673 register 1 Gateway 0187.049.001 IP 010.0187.049	168-179-673
Sheet

 

[psyracusa]

that worked perfectly! Many thanks!

 

[István Hirsch]

You are welcome, thanks for the feedback.

 

[Rick Rothstein]

I need to extract the S/N # (sn) and it's always ###-###-### that I bolded above and paste it into the adjacent next column (L)

Just so you know, the normal pattern for Social Security Numbers is nine digits, but not patterned the way you show in your question; rather, the pattern is this... ###-##-####. The description of the parts between the dashes can be found in this article...

Social Security number - Wikipedia, the free encyclopedia