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
1	zzzzzzz-03 feb 2019	03	feb	2019

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell 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) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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)

raw		raw.1	raw.2	raw.3
abc edr xyz 03 feb 2019		03	feb	2019
eer 3 feb 2019		3	feb	2019
wwwwwww 3 february 2019		3	february	2019
03 february 2019		03	february	2019

[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]