Extract 7 digit number from email subject

BrianMH

Well-known Member

Joined

Mar 26, 2008

Messages

1,490

• Apr 16, 2014

• #2

Will the subjects always have 2 # signs and the second always be 3 positions prior to your number?

 

Upvote 0

R

ravikabadwal

New Member

Joined

Apr 16, 2014

Messages

10

• Apr 16, 2014

• #3

Not really. Below are some more examples of the subject lines.

FW: ***ISSUE***4.14.14 - LINX HOTEL INTERNATIONAL AIRPORT GALEAO - GJP - #3936905 - SABRE GPS - Expected result 3936905
FW: 04.18.14 CAMINO REAL MEXICO CITY 3612457 GPS- Expected result 3612457
FW: 5.1.14 - HOLIDAY INN ACAPULCO RESORT - #3720111 - DRU- Expected result 3720111

Ravi

 

Upvote 0

P

pgc01

MrExcel MVP

Joined

Apr 25, 2006

Messages

19,897

• Apr 16, 2014

• #4

Hi Ravi
Welcome to the board

Try:

=MID(A1,MATCH(0,MMULT(-ISERROR(-MID(A1,ROW(INDIRECT("1:"&LEN(A1)-6))+{0,1,2,3,4,5,6},1)),{1;1;1;1;1;1;1}),0),7)

 

Upvote 0

R

ravikabadwal

New Member

Joined

Apr 16, 2014

Messages

10

• Apr 16, 2014

• #5

Thanks PGC.. This is great and works fine.
Is it possible to extract the dates also from the examples below as they have different date formats.

FW: 04/15/14** ORIENT EXPRESS HOTELS ITALIA SRL * CAMPAIGN #: 3799264 FLORENCE -GALILEO DRU + SABRE DRU-
FW: ***ISSUE***4.14.14 - LINX HOTEL INTERNATIONAL AIRPORT GALEAO - GJP - #3936905 - SABRE GPS - Expected result 3936905
FW: 04.18.14 CAMINO REAL MEXICO CITY 3612457 GPS- Expected result 3612457
FW: 5.1.14 - HOLIDAY INN ACAPULCO RESORT - #3720111 - DRU- Expected result 3720111
FW: ****ISSUEE****: 4/14/2014 - NORDIC LIGHT HOTEL - CAMPAIGN 3869697 - SABRE, GALILEO TEXT

Thanks once again for your reply.

 

Upvote 0

P

pgc01

MrExcel MVP

Joined

Apr 25, 2006

Messages

19,897

• Apr 16, 2014

• #6

Hi Ravi

When extracting values you should specify exactly its format.

I see in the examples 4 formats for the dates

m.dd.yyyy
mm.dd.yyyy
m/dd/yyyy
mm/dd/yyyy

Are there any other possible date formats in your data?

Also do you want a date value or the string?

 

Upvote 0

P

pgc01

MrExcel MVP

Joined

Apr 25, 2006

Messages

19,897

• Apr 16, 2014

• #7

Sorry, for the formats I meant:

m.dd.yy
mm.dd.yy
m/dd/yy
mm/dd/yy

Are there any other possible date formats in your data?

Also do you want a date value or the string?

 

Upvote 0

R

ravikabadwal

New Member

Joined

Apr 16, 2014

Messages

10

• Apr 16, 2014

• #8

I would prefer the date value in the format mm.dd.yy

Also, at the moment these are the only possible formats. If any other come up I'll ask you.


 

Upvote 0

P

pgc01

MrExcel MVP

Joined

Apr 25, 2006

Messages

19,897

• Apr 16, 2014

• #9

ravikabadwal said:

I would prefer the date value in the format mm.dd.yy

Click to expand...

OK.

ravikabadwal said:

Also, at the moment these are the only possible formats.

Click to expand...

Well, I read you examples again and found another one:

m.d.yy

So the list of possible formats is (until now)

m.d.yy
m.dd.yy
mm.dd.yy
m/dd/yy
mm/dd/yy

ravikabadwal said:

If any other come up I'll ask you.

Click to expand...

Sorry, but I may not be available for that. You should spend some time analysing your problem so that you can specify the possible formats.
Then post back with the complete list and I'll try to help.

 

Upvote 0

R

ravikabadwal

New Member

Joined

Apr 16, 2014

Messages

10

• Apr 16, 2014

• #10

Hi PGC,

Here is the complete list.

m.d.yy
m.dd.yy
mm.dd.yy
mm.d.yy
mm.d.yyyy
m.d.yyyy
m.dd.yyyy
mm.dd.yyyy
m/dd/yy
mm/dd/yy
m/dd/yyyy
mm/dd/yyyy
m/d/yy
m/d/yyyy
mm/d/yy
mm/d/yyyy

 

Upvote 0