How to extract text and number from a sentence. What formula should I use?

[improvemyexcel]

hi

here is the sample data.
I’ve tried using FIND, SUBSTITUTE, MID, LEFT, RIGHT, SEARCH…but to no avail. Help!

state	summary	Results
NY	task-sweep 2/3/16 19:54 coupon 123456 joe.doe@domain.com, jane.doe@domain.com, bugsbunny@wonderworld.com	[how do i get this column to extract coupon 123456 from my summary/QUOTE]

<tbody>

[TD][/TD]
[TD][/TD]

[TR]
[TD]CA[/TD]
[TD]mop 6/13/2016 10:03 coupon 678954 donald.duck@aircastle.com, ms.daisy@aircastle.com, whoisscrooge@moneyvault.com [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MA[/TD]
[TD]Tasks - polish 4/5/16 16:16 coupon 345678 bud.weiser@beer.com, applecider@sugarshack.com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LA[/TD]
[TD]hoover the floor 5/14/2016 08:45 coupon 253647 peskier@sea.com, turtle@land.com, unicorn@neverland.com
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>

 

[Michael M]

Maybe

Excel 2007
	A	B	C
1	NY	task-sweep 2/3/16 19:54coupon 123456joe.doe@domain.com,jane.doe@domain.com,bugsbunny@wonderworld.com	coupon 123456
2	CA	mop 6/13/2016 10:03 coupon [email]678954donald.duck@aircastle.com,ms.daisy@aircastle.com,whoisscrooge@moneyvault.com[/email]	coupon 678954
3	MA	Tasks - polish 4/5/16 16:16 coupon 345678bud.weiser@beer.com,applecider@sugarshack.com	coupon 345678
4	LA	hoover the floor 5/14/2016 08:45 coupon 253647peskier@sea.com,turtle@land.com,unicorn@neverland.com	coupon 253647
Sheet1

Cell Formulas
Range	Formula
C1	=MID(B1,FIND("coupon",B1,1),13)
C2	=MID(B2,FIND("coupon",B2,1),13)
C3	=MID(B3,FIND("coupon",B3,1),13)
C4	=MID(B4,FIND("coupon",B4,1),13)

 

[improvemyexcel]

Hi Michael,

In my haste and preoccupied state of my excel report, I had clicked send too quickly, hence my missing manners.
Nonetheless, thank you for your reply , and assistance in my issue, I will give it a try when I get back to work in the AM.

Best regards,

 

[improvemyexcel]

Hi everyone,

Mike's formula and advise worked.

I'm just now realizing that i have additional items in the row, which I hadn't accounted for yesterday when I posted my question.

Could you please have a look at it, and let me know what other methods I can explore in extracting the coupon number?

Thank you all!!

A	B	C
NY	task-sweep 2/3/16 19:54 coupon 123456 joe.doe@domain.com,jane.doe@domain.com,bugsbunny@wonderworld.com tasks- mop and sweep 5/5/2016 16:16 coupon 789456 mary.hadalittlelamb@greenpasture.com, herecomespolly@moppy.com	need to see in this column: "coupon 123456" "coupon 789456"
CA	mop 6/13/2016 10:03 coupon 678954donald.duck@aircastle.com,ms.d...moneyvault.com
MA	Tasks - polish 4/5/16 16:16 coupon 345678 bud.weiser@beer.com,applecider@sugarshack.com task- mow lawn, pick garbage 8/8/2016 14:50 coupon 345789 greenfly@mush.com, beetle.juice@darkworld.com
LA	hoover the floor 5/14/2016 08:45 coupon 253647 peskier@sea.com,turtle@land.com,unicorn@neverland.com

<tbody>
</tbody><colgroup><col><col><col></colgroup>

 

[István Hirsch]

How many "coupons" can be in a cell (maximum)? Does the number to extract always contain six digits?

 

[improvemyexcel]

Hi Istvan
There isn’t a maximum so to speak. Sometimes it could be as little as 1, and maximum could be anything.
For now, it’s always 6 digits…but it will increase at some point in time to 7 or maybe 8. There’s also a chance it will be alphanumeric.

 

[István Hirsch]

Hi Istvan
There isn’t a maximum so to speak. Sometimes it could be as little as 1, and maximum could be anything.
For now, it’s always 6 digits…but it will increase at some point in time to 7 or maybe 8. There’s also a chance it will be alphanumeric.

What is the extract from row CA if it can be alphanumeric and more than 6 characters long?

 

[Michael M]

Assuming your data is in Column "B", try this VBA

Sub MM1()
Dim regex As Object, str As String, lr As Long, r As Long, lc As Integer
lr = Cells(Rows.Count, "B").End(xlUp).Row
Set regex = CreateObject("VBScript.RegExp")
    With regex
      .Pattern = "coupon ([0-9]+)"
      .Global = True
    End With
    For r = 1 To lr
        str = Range("B" & r).Value
        Set matches = regex.Execute(str)
            For Each Match In matches
                lc = Cells(r, Columns.Count).End(xlToLeft).Column
            Cells(r, lc + 1).Value = Match.Value
        Next Match
    Next r
End Sub