# How to split dates from end of text strong?

#### Magnatolia

##### Board Regular
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!

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Maybe this

Excel 2007
ABCD
1zzzzzzz-03 feb 201903feb2019
Sheet1
Cell Formulas
RangeFormula
B1=MID(A1,FIND("-",A1)+1,2)
C1=MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1))
D1=RIGHT(A1,4)

Last edited:
Or try:

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

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

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

Maybe this
Excel 2007
ABCD
1zzzzzzz-03 feb 201903feb 2019

</tbody>
Sheet1

Worksheet Formulas
CellFormula
B1=MID(A1,FIND("-",A1)+1,2)
C1=MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1))
D1=RIGHT(A1,4)

</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!

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

Code:
``````[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]``````

Replies
5
Views
244
Replies
16
Views
563
Replies
5
Views
66
Replies
1
Views
476
Replies
8
Views
397

1,203,687
Messages
6,056,746
Members
444,888
Latest member
Babi_mn

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back