Excel Workbook |
---|
|
---|
| A | B |
---|
1 | FW: 04.15.2014 - THE MANHATTAN HOTEL ROTTERDAM BID #: 8399 / GDS CAMPAIGN #: 3672287 SABRE GPS DRU | 04.15.2014 |
---|
2 | FW: ***ISSUE***4.14.14 - LINX HOTEL INTERNATIONAL AIRPORT GALEAO - GJP - #3936905 - SABRE GPS - Expected result 3936905 | 04.14.2014 |
---|
3 | FW: 04.18.14 CAMINO REAL MEXICO CITY 3612457 GPS- Expected result 3612457 | 04.18.2014 |
---|
4 | FW: 5.1.14 - HOLIDAY INN ACAPULCO RESORT - #3720111 - DRU- Expected result 3720111 | 05.01.2014 |
---|
5 | FW: 04/15/14** ORIENT EXPRESS HOTELS ITALIA SRL * CAMPAIGN #: 3799264 FLORENCE -GALILEO DRU + SABRE DRU- | 04.15.2014 |
---|
6 | FW: ***ISSUE***4.14.14 - LINX HOTEL INTERNATIONAL AIRPORT GALEAO - GJP - #3936905 - SABRE GPS - Expected result 3936905 | 04.14.2014 |
---|
7 | FW: 04.18.14 CAMINO REAL MEXICO CITY 3612457 GPS- Expected result 3612457 | 04.18.2014 |
---|
8 | FW: 5.1.14 - HOLIDAY INN ACAPULCO RESORT - #3720111 - DRU- Expected result 3720111 | 05.01.2014 |
---|
9 | FW: ****ISSUEE****: 4/14/2014 - NORDIC LIGHT HOTEL - CAMPAIGN 3869697 - SABRE, GALILEO TEXT | 04.14.2014 |
---|
10 | FW: ****ISSUEE****: 11/14/2014 - NORDIC LIGHT HOTEL - CAMPAIGN 3869697 - SABRE, GALILEO TEXT | 11.14.2014 |
---|
|
---|
I tried to create a formula solution. Enter it into B1 and copy down. There can not be "." or "/" character preceding the date in the string. YY is supposed to be 20YY.
=TEXT(LOOKUP(13,--MID(SUBSTITUTE(A1,"/","."),FIND(".",SUBSTITUTE(A1,"/","."))-{1,2},{1,2})),"00")&"."&TEXT(LOOKUP(32,--MID(SUBSTITUTE(A1,"/","."),FIND(".",SUBSTITUTE(A1,"/","."))+1,{1,2})),"00")&"."&IFERROR(--MID(SUBSTITUTE(A1,"/","."),FIND(".",SUBSTITUTE(A1,"/","."),FIND(".",SUBSTITUTE(A1,"/","."))+1)+1,4),"20"&MID(SUBSTITUTE(A1,"/","."),FIND(".",SUBSTITUTE(A1,"/","."),FIND(".",SUBSTITUTE(A1,"/","."))+1)+1,2))