SOLVED

Date Extraction

%3CLINGO-SUB%20id%3D%22lingo-sub-198999%22%20slang%3D%22en-US%22%3EDate%20Extraction%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-198999%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3EI'd%20need%20an%20additional%20help%20please.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20have%20a%20string%20containing%20this%20text%20%3A%20%5B09OCT18-16DEC18%5E30DEC18-01JAN19%5E12JAN19-31MAR19%5D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'd%20need%20to%20read%20it%20like%20%22from%2009OCT18%20to%2016DEC18%20and%20from%2030DEC18%20to%2001%20JAN18%20and%20from%2012JAN19%20to%2031MAR19%22%2C%20is%20there%20a%20way%20to%20extract%20the%20couples%20and%20have%20the%20output%20in%20different%20cells%3F%20I'd%20need%20the%20following%20output%2C%20using%20this%20table%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3CP%3EStart%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BEnd%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E09%2F10%2F2018%3C%2FTD%3E%3CTD%3E16%2F12%2F2018%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E30%2F12%2F2018%3C%2FTD%3E%3CTD%3E01%2F01%2F2019%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E12%2F01%2F2019%3C%2FTD%3E%3CTD%3E31%2F03%2F2019%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20very%20much%20for%20your%20help!%3C%2FP%3E%3CP%3EMarco%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-198999%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Edata%20extraction%20formula%20excel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20%26amp%3B%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-199130%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Extraction%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-199130%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Macro%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20can%20be%20done%20by%20using%20Power%20Query%20which%20is%20also%20known%20as%20(%3CSTRONG%3EGet%20%26amp%3B%20Transform%20Data%3C%2FSTRONG%3E)%20in%20Excel%202016%2C%20and%20if%20you%20don't%20have%20Excel%202016%2C%20then%20you%20have%20to%20download%20and%20install%20%3CA%20href%3D%22https%3A%2F%2Fwww.microsoft.com%2Fen-us%2Fdownload%2Fdetails.aspx%3Fid%3D39379%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EPower%20Query%20Add-in%3C%2FA%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20find%20the%20attached%20file%20and%20try%20to%20update%20the%20left%20table%2C%20then%20right-click%20on%20the%20right%20table%20and%20select%20Refresh%20%3CSPAN%20class%3D%22%22%3Eto%20see%20how%20it%20will%20be%20updated.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

 

Hi All,

I'd need an additional help please.  

If I have a string containing this text : [09OCT18-16DEC18^30DEC18-01JAN19^12JAN19-31MAR19]

 

I'd need to read it like "from 09OCT18 to 16DEC18 and from 30DEC18 to 01 JAN18 and from 12JAN19 to 31MAR19", is there a way to extract the couples and have the output in different cells? I'd need the following output, using this table: 

       

Start                               End

09/10/201816/12/2018
30/12/201801/01/2019
12/01/201931/03/2019

 

Thank you very much for your help!

Marco

1 Reply
Highlighted
Best Response confirmed by Marco Verace (Occasional Contributor)
Solution

Hi Macro,

 

This can be done by using Power Query which is also known as (Get & Transform Data) in Excel 2016, and if you don't have Excel 2016, then you have to download and install Power Query Add-in.

 

Please find the attached file and try to update the left table, then right-click on the right table and select Refresh to see how it will be updated.

 

Regards