How to split dates from end of text strong?

[Magnatolia]

Hi,

I am building a document auditing spreadsheet. All of our documents should have " -dd mmm yyyy" at the end. I want to extract these characters into three columns. I can pull the day using RIGHT formula as these cannot have a zero such as 03. But I'm struggling with the month and year. I can pull the whole date and then do text to columns. But I'd prefer something dynamic.

Alternatively, is there a way of comparing through conditional formatting a column against a named range? I could then higlight anywhere where the full month appears. But I'm not sure if conditional formatting is that powerful.

Thanks!

 

[Michael M]

Maybe this

Excel 2007
	A	B	C	D
1	zzzzzzz-03 feb 2019	03	feb	2019
Sheet1

Cell Formulas
Range	Formula
B1	=MID(A1,FIND("-",A1)+1,2)
C1	=MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1))
D1	=RIGHT(A1,4)

 

[Phuoc]

Or try:

=DAY(--TRIM(RIGHT(SUBSTITUTE(A1,"-",REPT(" ",100)),100)))

=MONTH(--TRIM(RIGHT(SUBSTITUTE(A1,"-",REPT(" ",100)),100)))

=YEAR(--TRIM(RIGHT(SUBSTITUTE(A1,"-",REPT(" ",100)),100)))

 

[Magnatolia]

Maybe this
Excel 2007

	A	B	C	D
zzzzzzz-03 feb 2019	feb

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]03[/TD]

[TD="align: right"]2019[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]B1[/TH]
[TD="align: left"]=MID(A1,FIND("-",A1)+1,2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]C1[/TH]
[TD="align: left"]=MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]D1[/TH]
[TD="align: left"]=RIGHT(A1,4)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

This is great, thanks! With C1 formula, how do I find the space at the end? Unfortunately there is a space between the text (zzzzz) and date. I need to extract the full word between the day and year i.e could be Feb or February or Fbruary for example.

Thanks!

 

[sandy666]

alternative (PowerQuery - Get&Transform)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]raw[/td][td][/td][td=bgcolor:#70AD47]raw.1[/td][td=bgcolor:#70AD47]raw.2[/td][td=bgcolor:#70AD47]raw.3[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]abc edr xyz 03 feb 2019[/td][td][/td][td=bgcolor:#E2EFDA]03[/td][td=bgcolor:#E2EFDA]feb[/td][td=bgcolor:#E2EFDA]2019[/td][/tr]

[tr=bgcolor:#FFFFFF][td]eer 3 feb 2019[/td][td][/td][td]3[/td][td]feb[/td][td]2019[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]wwwwwww 3 february 2019[/td][td][/td][td=bgcolor:#E2EFDA]3[/td][td=bgcolor:#E2EFDA]february[/td][td=bgcolor:#E2EFDA]2019[/td][/tr]

[tr=bgcolor:#FFFFFF][td]03 february 2019[/td][td][/td][td]03[/td][td]february[/td][td]2019[/td][/tr]
[/table]

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ExtractAD = Table.TransformColumns(Source, {{"raw", each Text.AfterDelimiter(_, " ", {2, RelativePosition.FromEnd}), type text}}),
    Split = Table.SplitColumn(ExtractAD, "raw", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"raw.1", "raw.2", "raw.3"}),
    Trim = Table.TransformColumns(Split,{{"raw.1", Text.Trim, type text}, {"raw.2", Text.Trim, type text}, {"raw.3", Text.Trim, type text}})
in
    Trim[/SIZE]