How to split dates from end of text strong?

Magnatolia

Board Regular
Joined
Jan 19, 2012
Messages
79
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!
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,267
Office Version
  1. 2013
Platform
  1. Windows
Maybe this
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">zzzzzzz-03 feb 2019</td><td style="text-align: right;;">03</td><td style=";">feb </td><td style="text-align: right;;">2019</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B1</th><td style="text-align:left">=MID(<font color="Blue">A1,FIND(<font color="Red">"-",A1</font>)+1,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C1</th><td style="text-align:left">=MID(<font color="Blue">A1,FIND(<font color="Red">" ",A1</font>)+1,FIND(<font color="Red">" ",A1,FIND(<font color="Green">" ",A1</font>)+1</font>)-FIND(<font color="Red">" ",A1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D1</th><td style="text-align:left">=RIGHT(<font color="Blue">A1,4</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

Phuoc

Board Regular
Joined
Apr 29, 2016
Messages
209
Or try:

Code:
=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

Board Regular
Joined
Jan 19, 2012
Messages
79
Maybe this
Excel 2007
ABCD
1zzzzzzz-03 feb 201903feb 2019

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</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)

<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

Well-known Member
Joined
Oct 24, 2015
Messages
6,778
alternative (PowerQuery - Get&Transform)

rawraw.1raw.2raw.3
abc edr xyz 03 feb 201903feb2019
eer 3 feb 20193feb2019
wwwwwww 3 february 20193february2019
03 february 201903february2019

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]
 

Watch MrExcel Video

Forum statistics

Threads
1,109,227
Messages
5,527,539
Members
409,769
Latest member
Hudz

This Week's Hot Topics

Top