Extract text from text string

[aslante]

I have a text string that looks like this:

[FONT=&quot]RRULE:FREQ=WEEKLY;WKST=MO;UNTIL=[/FONT][FONT=&quot]20190627[/FONT][FONT=&quot];BYDAY=[/FONT][FONT=&quot]SA,TH[/FONT][FONT=&quot]

I want to extract

1) the text in red which is essentially a date. After extracting it, it should convert into dd/mm/yyyy (06/06/2019) format.

2) the text in blue which are the days

Any help is greatly appreciated!

Thanks in advance!


[/FONT]

 

[sandy666]

try PowerQuery (Get&Transform)

source		Date	Days
RRULE:FREQ=WEEKLY;WKST=MO;UNTIL=20190627;BYDAY=SA,TH		20190627	SA,TH

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Between = Table.AddColumn(Source, "Date", each Text.BetweenDelimiters([source], "UNTIL=", ";"), type text),
    After = Table.AddColumn(Between, "Days", each Text.AfterDelimiter([source], "=", {0, RelativePosition.FromEnd}), type text),
    ROC = Table.SelectColumns(After,{"Date", "Days"})
in
    ROC[/SIZE]

 

[FormR]

Hi, it's usually best to give a few varying examples of the strings with these types of questions.

Here are some formula based options you can try. For the date field you can format as desired.

Excel 2013/2016
	A	B	C
2	RRULE:FREQ=WEEKLY;WKST=MO;UNTIL=20190627;BYDAY=SA,TH	27/06/2019	SA,TH
Sheet1

Cell Formulas
Range	Formula
B2	=0+TEXT(MID(A2,FIND("UNTIL=",A2)+6,8),"0000-00-00")
C2	=TRIM(RIGHT(SUBSTITUTE(A2,"=",REPT(" ",255)),255))