Extract dates from text string

T

Tvynn

New Member
Joined
Oct 18, 2018
Messages
10
Hi, I'd like to extract out the dates from text string by using formula instead of text to column, is it possible? After that, i'd like to calculate the Number of Days Between Two Dates, and shows it blank if nothing to deduct instead of showing date. Appreciate your kind help, thanks!

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 20px"><col width="628"><col width="58"><col width="58"><col width="58"><col width="58"><col width="63"><col width="63"><col width="63"><col width="74"></colgroup><tbody>[TR]
[TD][/TD]
[TD]Original text[/TD]
[TD="bgcolor: #FFFF00"]date 1[/TD]
[TD="bgcolor: #FFFF00"]date 2[/TD]
[TD="bgcolor: #FFFF00"]date 3[/TD]
[TD="bgcolor: #FFFF00"]date 4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7E1CD]#B7E1CD[/URL] , colspan: 3"]Number of Days Between Two Dates[/TD]
[TD="bgcolor: #C9DAF8"]Number of Days Between First & Last Date

[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]a@mail.com/hard(amy/2jan19);b@mail.com/hard(ken/20dec18);c@mail.com/hard(jane/01nov18);d@mail.com/hard(bill/15oct18);[/TD]
[TD="align: right"]2Jan19[/TD]
[TD="align: right"]20Dec18[/TD]
[TD="align: right"]01Nov18[/TD]
[TD="align: right"]15Oct18[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]79[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]a@mail.com/hard(amy/3jan19);b@mail.com/hard(jane/5dec18);c@mail.com/hard(kate/15oct18);[/TD]
[TD="align: right"]3Jan19[/TD]
[TD="align: right"]05Dec18[/TD]
[TD="align: right"]15Oct18[/TD]
[TD][/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]51[/TD]
[TD][/TD]
[TD="align: right"]80[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]a@mail.com/hard(amy/9jan19);b@mail.com/hard(jane/8dec18);[/TD]
[TD="align: right"]9Jan19[/TD]
[TD="align: right"]8Dec18[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]32[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]32[/TD]
[/TR]
</tbody>[/TABLE]
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style><style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
you have jane and ken at the same location, will that not create an issue in data quality ?
 
Upvote 0
Hi Mole,

Nope those names will not create any issue, what i want is to extract out those dates only

Thanks


Tvynn said:
Hi, I'd like to extract out the dates from text string by using formula instead of text to column, is it possible? After that, i'd like to calculate the Number of Days Between Two Dates, and shows it blank if nothing to deduct instead of showing date. Appreciate your kind help, thanks!

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<tbody>[TR]
[TD][/TD]
[TD]Original text[/TD]
[TD="bgcolor: #FFFF00"]date 1[/TD]
[TD="bgcolor: #FFFF00"]date 2[/TD]
[TD="bgcolor: #FFFF00"]date 3[/TD]
[TD="bgcolor: #FFFF00"]date 4[/TD]
[TD="colspan: 3"]Number of Days Between Two Dates[/TD]
[TD="bgcolor: #C9DAF8"]Number of Days Between First & Last Date
[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]a@mail.com/hard(amy/2jan19);b@mail.com/hard(ken/20dec18);c@mail.com/hard(jane/01nov18);d@mail.com/hard(bill/15oct18);[/TD]
[TD="align: right"]2Jan19[/TD]
[TD="align: right"]20Dec18[/TD]
[TD="align: right"]01Nov18[/TD]
[TD="align: right"]15Oct18[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]79[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]a@mail.com/hard(amy/3jan19);b@mail.com/hard(jane/5dec18);c@mail.com/hard(kate/15oct18);[/TD]
[TD="align: right"]3Jan19[/TD]
[TD="align: right"]05Dec18[/TD]
[TD="align: right"]15Oct18[/TD]
[TD][/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]51[/TD]
[TD][/TD]
[TD="align: right"]80[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]a@mail.com/hard(amy/9jan19);b@mail.com/hard(jane/8dec18);[/TD]
[TD="align: right"]9Jan19[/TD]
[TD="align: right"]8Dec18[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]32[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]32[/TD]
[/TR]
</tbody>[/TABLE]
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style><style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>
Click to expand...
 
Upvote 0
you can try to play with PowerQuery:

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Text Between Delimiters[/td][td=bgcolor:#70AD47]Text Between Delimiters.1[/td][td=bgcolor:#70AD47]Text Between Delimiters.2[/td][td=bgcolor:#70AD47]Text Between Delimiters.3[/td][td=bgcolor:#70AD47]Subtraction[/td][td=bgcolor:#70AD47]Subtraction.1[/td][td=bgcolor:#70AD47]Subtraction.2[/td][td=bgcolor:#70AD47]Addition[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
02/01/2019​
[/td][td=bgcolor:#E2EFDA]
20/12/2018​
[/td][td=bgcolor:#E2EFDA]
01/11/2018​
[/td][td=bgcolor:#E2EFDA]
15/10/2018​
[/td][td=bgcolor:#E2EFDA]
13​
[/td][td=bgcolor:#E2EFDA]
49​
[/td][td=bgcolor:#E2EFDA]
17​
[/td][td=bgcolor:#E2EFDA]
79​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
03/01/2019​
[/td][td]
05/12/2018​
[/td][td]
15/10/2018​
[/td][td][/td][td]
29​
[/td][td]
51​
[/td][td][/td][td]
80​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
09/01/2019​
[/td][td=bgcolor:#E2EFDA]
08/12/2018​
[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]
32​
[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]
32​
[/td][/tr]
[/table]

Code: 
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Original text", type text}}),
    #"Inserted Text Between Delimiters" = Table.AddColumn(#"Changed Type", "Text Between Delimiters", each Text.BetweenDelimiters([Original text], "/", ")", 1, 0), type text),
    #"Inserted Text Between Delimiters1" = Table.AddColumn(#"Inserted Text Between Delimiters", "Text Between Delimiters.1", each Text.BetweenDelimiters([Original text], "/", ")", 3, 0), type text),
    #"Inserted Text Between Delimiters2" = Table.AddColumn(#"Inserted Text Between Delimiters1", "Text Between Delimiters.2", each Text.BetweenDelimiters([Original text], "/", ")", 5, 0), type text),
    #"Inserted Text Between Delimiters3" = Table.AddColumn(#"Inserted Text Between Delimiters2", "Text Between Delimiters.3", each Text.BetweenDelimiters([Original text], "/", ")", 7, 0), type text),
    #"Parsed Date" = Table.TransformColumns(#"Inserted Text Between Delimiters3",{{"Text Between Delimiters", each Date.From(DateTimeZone.From(_)), type date}, {"Text Between Delimiters.1", each Date.From(DateTimeZone.From(_)), type date}, {"Text Between Delimiters.2", each Date.From(DateTimeZone.From(_)), type date}, {"Text Between Delimiters.3", each Date.From(DateTimeZone.From(_)), type date}}),
    #"Inserted Date Subtraction" = Table.AddColumn(#"Parsed Date", "Subtraction", each Duration.Days([Text Between Delimiters] - [Text Between Delimiters.1]), Int64.Type),
    #"Inserted Date Subtraction1" = Table.AddColumn(#"Inserted Date Subtraction", "Subtraction.1", each Duration.Days([Text Between Delimiters.1] - [Text Between Delimiters.2]), Int64.Type),
    #"Inserted Date Subtraction2" = Table.AddColumn(#"Inserted Date Subtraction1", "Subtraction.2", each Duration.Days([Text Between Delimiters.2] - [Text Between Delimiters.3]), Int64.Type),
    #"Inserted Date Subtraction3" = Table.AddColumn(#"Inserted Date Subtraction2", "Subtraction.3", each Duration.Days([Text Between Delimiters] - [Text Between Delimiters.3]), Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Date Subtraction3",{"Subtraction.3"}),
    #"Inserted Sum" = Table.AddColumn(#"Removed Columns", "Addition", each List.Sum({[Subtraction], [Subtraction.1], [Subtraction.2]}), Int64.Type),
    #"Removed Columns1" = Table.RemoveColumns(#"Inserted Sum",{"Original text"})
in
    #"Removed Columns1"[/SIZE]

M-code can be optimized but I'm too lazy ;)
 
Upvote 0
Hi Sandy,

Thanks for your kind help, but i never use this method before, seems like writing a macro code, guess need to insert file for it to work? Think i need to try up first

Thanks ya


mole999 said:
you have jane and ken at the same location, will that not create an issue in data quality ?
Click to expand...
 
Upvote 0
Assuming your data starts in cell A1, put this formula in cell B1 and copy it across for as many columns as you think you will ever have dates to fill, then copy all of those cells down to the bottom of your data...

=TRIM(RIGHT(SUBSTITUTE(TRIM(MID(SUBSTITUTE(")"&$A1,")",REPT(" ",500)),COLUMNS($B:B)*500,500)),"/",REPT(" ",500)),500))

Note: I am assuming none of your cells in Column A will ever contain more than 500 characters total.
 
Last edited:
Upvote 0
Thanks so much for your help Rick! it's easy and worked perfectly! And thanks also Sandy eventhough im not able to try up at the moment.

By the way, i have further step to do, that is after getting the number of days between 2 dates, if it's more than 30 days, then i would like to remove the statement between two ";" from the text string as red font below, wonder if it's possible? Else i may use another method as well, thanks again and god bless!
[TABLE="width: 0"]
<colgroup><col style="width: 27px;"><col width="611"><col width="61"><col width="61"><col width="61"><col width="61"><col width="61"><col width="44"><col width="44"><col width="44"></colgroup><tbody>[TR]
[TD="align: right"][FONT=&quot]1[/FONT][/TD]
[TD][FONT=&quot]a@mail.com/hard(amy/2jan19);b@mail.com/hard(ken/20dec18);c@mail.com/hard(jane/01nov18);d@mail.com/hard(bill/15oct18);[/FONT][/TD]
[TD="bgcolor: #FAFAFA"][FONT=&quot]2jan19[/FONT][/TD]
[TD="bgcolor: #FAFAFA"][FONT=&quot]20dec18[/FONT][/TD]
[TD="bgcolor: #FAFAFA"][FONT=&quot]01nov18[/FONT][/TD]
[TD="bgcolor: #FAFAFA"][FONT=&quot]15oct18[/FONT][/TD]
[TD="bgcolor: #FAFAFA"][FONT=&quot];[/FONT][/TD]
[TD="bgcolor: #FAFAFA"][FONT=&quot]13[/FONT][/TD]
[TD="bgcolor: #FFFF00"][FONT=&quot]49[/FONT][/TD]
[TD="bgcolor: #FAFAFA"][FONT=&quot]17[/FONT][/TD]
[/TR]
[TR]
[TD="align: right"][FONT=&quot]2[/FONT][/TD]
[TD][FONT=&quot]a@mail.com/hard(amy/3jan19);b@mail.com/hard(jane/5dec18);c@mail.com/hard(kate/15oct18);[/FONT][/TD]
[TD="bgcolor: #FAFAFA"][FONT=&quot]3jan19[/FONT][/TD]
[TD="bgcolor: #FAFAFA"][FONT=&quot]5dec18[/FONT][/TD]
[TD="bgcolor: #FAFAFA"][FONT=&quot]15oct18[/FONT][/TD]
[TD="bgcolor: #FAFAFA"][FONT=&quot];[/FONT][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][FONT=&quot]29[/FONT][/TD]
[TD="bgcolor: #FFFF00"][FONT=&quot]51[/FONT][/TD]
[TD="bgcolor: #FAFAFA"][FONT=&quot]#VALUE![/FONT][/TD]
[/TR]
[TR]
[TD="align: right"][FONT=&quot]3[/FONT][/TD]
[TD][FONT=&quot]a@mail.com/hard(amy/9jan19);b@mail.com/hard(jane/8dec18);[/FONT][/TD]
[TD="bgcolor: #FAFAFA"][FONT=&quot]9jan19[/FONT][/TD]
[TD="bgcolor: #FAFAFA"][FONT=&quot]8dec18[/FONT][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FFFF00"][FONT=&quot]32[/FONT][/TD]
[TD="bgcolor: #FAFAFA"][FONT=&quot]FALSE[/FONT][/TD]
[TD="bgcolor: #FAFAFA"][FONT=&quot]FALSE[/FONT][/TD]
[/TR]
[TR]
[TD="align: right"][FONT=&quot]4[/FONT][/TD]
[TD][FONT=&quot]a@mail.com/hard(amy/9jan19)[/FONT][/TD]
[TD="bgcolor: #FAFAFA"][FONT=&quot]9jan19[/FONT][/TD]
[TD="bgcolor: #FAFAFA"][FONT=&quot];[/FONT][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][FONT=&quot]#VALUE![/FONT][/TD]
[TD="bgcolor: #FAFAFA"][FONT=&quot]FALSE[/FONT][/TD]
[TD="bgcolor: #FAFAFA"][FONT=&quot]FALSE[/FONT][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
@Rick, this is very useful, im looking for this same solution but my source is different, how should be modified if i need to extract dates from a text cell like this:
_________________________________________
[TABLE="width: 450"]
<tbody>[TR]
[TD="class: xl63, width: 450"]Pardot - Database - Automation Rules (unlimited)
1 license x $0.00/mo x 8 months

Term: 5/31/2019 - 1/30/2020[/TD]
[/TR]
</tbody>[/TABLE]
__________________________________

The word "term", will not always be there, thats why Im looking for a way to recognize the date format and extract 1 by 1 into separate columns.

Any help will be highly appreciated.
 
Last edited:
Upvote 0
Rick Rothstein said:
Assuming your data starts in cell A1, put this formula in cell B1 and copy it across for as many columns as you think you will ever have dates to fill, then copy all of those cells down to the bottom of your data...

=TRIM(RIGHT(SUBSTITUTE(TRIM(MID(SUBSTITUTE(")"&$A1,")",REPT(" ",500)),COLUMNS($B:B)*500,500)),"/",REPT(" ",500)),500))

Note: I am assuming none of your cells in Column A will ever contain more than 500 characters total.
Click to expand...


@Rick, this is very useful, im looking for this same solution but my source is different, how should be modified if i need to extract dates from a text cell like this:

[TABLE="width: 450"]
<tbody>[TR]
[TD="class: xl63"]Pardot - Database - Automation Rules (unlimited)
1 license x $0.00/mo x 8 months

Term: 5/31/2019 - 1/30/2020[/TD]
[/TR]
</tbody>[/TABLE]


The word "term", will not always be there, thats why Im looking for a way to recognize the date format and extract 1 by 1 into separate columns.

Any help will be highly appreciated.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Help with separating Text String from a Excel Col
Replies
3
Views
605
Peter_SSs
Peter_SSs
M
  • Question Question
Please help!! Need help with specialized formula.
Replies
3
Views
972
Eric W
Eric W
A
  • Question Question
Filter dataset with multiple parameters
Replies
3
Views
4K
JLGWhiz
JLGWhiz
S
  • Question Question
Search a table and return rows based on criteria of 2 cells
Replies
1
Views
723
Fluff
Fluff
W
  • Question Question
Converting a Hexadecimal with a length of 14 to Hexadecimal
Replies
6
Views
2K
joeu2004
J

Forum statistics

Threads
1,224,823
Messages
6,181,169
Members
453,021
Latest member
Justyna P

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top