Extract Date

How_Do_I

Well-known Member
Joined
Oct 23, 2009
Messages
1,831
Office Version
  1. 2010
Platform
  1. Windows
Hello I have data in Column A and would like to extract that data in date format in to Columns B and C... How would I go about that please...?

Excel Workbook
ABC
21 (39)-50 (39), 1939 3 Sep-25 Sep (PDF, 106.611 MB)03-Sep-193925-Sep-1939
351 (39)-100 (39), 1939 21 Sep-27 Oct (PDF, 97.278 MB)21-Sep-193927-Oct-1939
4101 (39)-150 (39), 1939 28 Oct-6 Dec (PDF, 78.001 MB)28-Oct-193906-Dec-1939
Sheet1
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
This can be accomplished with Power Query so long as you are using Excel 2010 or later. Here is the Mcode for that

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Column1.2", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Column1.2.1", "Column1.2.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1.2.1", type date}, {"Column1.2.2", type text}}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type2", "Column1.2.2", Splitter.SplitTextByDelimiter("(", QuoteStyle.Csv), {"Column1.2.2.1", "Column1.2.2.2"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Column1.2.2.1", type date}, {"Column1.2.2.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type3",{"Column1.1", "Column1.2.2.2", "Column1.3"}),
    #"Split Column by Delimiter3" = Table.SplitColumn(Table.TransformColumnTypes(#"Removed Columns", {{"Column1.2.1", type text}}, "en-US"), "Column1.2.1", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Column1.2.1.1", "Column1.2.1.2", "Column1.2.1.3"}),
    #"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"Column1.2.1.1", Int64.Type}, {"Column1.2.1.2", Int64.Type}, {"Column1.2.1.3", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type4",1939,2019,Replacer.ReplaceValue,{"Column1.2.1.3"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Replaced Value", {{"Column1.2.1.1", type text}, {"Column1.2.1.2", type text}, {"Column1.2.1.3", type text}}, "en-US"),{"Column1.2.1.1", "Column1.2.1.2", "Column1.2.1.3"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Merged"),
    #"Changed Type5" = Table.TransformColumnTypes(#"Merged Columns",{{"Merged", type date}})
in
    #"Changed Type5"
 
Upvote 0
little shorter

Code:
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    ExtractBD = Table.TransformColumns(Source, {{"Column1", each Text.BetweenDelimiters(_, ", ", " ("), type text}}),
    TextBD = Table.AddColumn(ExtractBD, "Text Before Delimiter", each Text.BeforeDelimiter([Column1], " "), type text),
    TextAD = Table.AddColumn(TextBD, "Text After Delimiter", each Text.AfterDelimiter([Column1], " "), type text),
    SplitByD = Table.SplitColumn(TextAD, "Text After Delimiter", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Text After Delimiter.1", "Text After Delimiter.2"}),
    Merge1 = Table.AddColumn(SplitByD, "1st date", each Text.Combine({[Text After Delimiter.1], [Text Before Delimiter]}, " "), type text),
    Merge2 = Table.AddColumn(Merge1, "2nd date", each Text.Combine({[Text After Delimiter.2], [Text Before Delimiter]}, " "), type text),
    Replace = Table.ReplaceValue(Merge2," ","-",Replacer.ReplaceText,{"1st date", "2nd date"}),
    ROC = Table.SelectColumns(Replace,{"1st date", "2nd date"})
in
    ROC[/SIZE]

Column11st date2nd date
1 (39)-50 (39), 1939 3 Sep-25 Sep (PDF, 106.611 MB)3-Sep-193925-Sep-1939
51 (39)-100 (39), 1939 21 Sep-27 Oct (PDF, 97.278 MB)21-Sep-193927-Oct-1939
101 (39)-150 (39), 1939 28 Oct-6 Dec (PDF, 78.001 MB)28-Oct-19396-Dec-1939
 
Upvote 0
And a formula way. Copy formulas down as needed.
Custom Format cells as dd-mmm-yyy
Excel Workbook
ABC
1
21 (39)-50 (39), 1939 3 Sep-25 Sep (PDF, 106.611 MB)03-Sep-193925-Sep-1939
351 (39)-100 (39), 1939 21 Sep-27 Oct (PDF, 97.278 MB)21-Sep-193927-Oct-1939
4101 (39)-150 (39), 1939 28 Oct-6 Dec (PDF, 78.001 MB)28-Oct-193906-Dec-1939
Sheet
 
Upvote 0
or even shorter then above (post#3)

Code:
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    ExtractBD = Table.TransformColumns(Source, {{"Column1", each Text.BetweenDelimiters(_, ", ", " ("), type text}}),
    Split1 = Table.SplitColumn(ExtractBD, "Column1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column1.1", "Column1.2"}),
    Split2 = Table.SplitColumn(Split1, "Column1.2", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Column1.2.1", "Column1.2.2"}),
    Merge1 = Table.AddColumn(Split2, "1st date", each Text.Combine({[Column1.2.1], [Column1.1]}, " "), type text),
    Merge2 = Table.AddColumn(Merge1, "2nd date", each Text.Combine({[Column1.2.2], [Column1.1]}, " "), type text),
    ROC = Table.SelectColumns(Merge2,{"1st date", "2nd date"}),
    RepVal = Table.ReplaceValue(ROC," ","-",Replacer.ReplaceText,{"1st date", "2nd date"})
in
    RepVal[/SIZE]
 
Last edited:
Upvote 0
Thanks everyone, I'm not too sure how to use power query so I'd have to watch a few videos on youtube... So I've used AhoyNC's formulas which are working brilliantly...!
 
Upvote 0
Hello I have data in Column A and would like to extract that data in date format in to Columns B and C... How would I go about that please...?

Sheet1

ABC
21 (39)-50 (39), 1939 3 Sep-25 Sep (PDF, 106.611 MB)03-Sep-193925-Sep-1939
351 (39)-100 (39), 1939 21 Sep-27 Oct (PDF, 97.278 MB)21-Sep-193927-Oct-1939
4101 (39)-150 (39), 1939 28 Oct-6 Dec (PDF, 78.001 MB)28-Oct-193906-Dec-1939

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:337px;"><col style="width:82px;"><col style="width:82px;"></colgroup><tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
What would the cell value look like if the second date crossed Dec 31st into the next year?
 
Upvote 0
What would the cell value look like if the second date crossed Dec 31st into the next year?

Yes, the returns are a #VALUE error however, this is a one off thing so, once I have the dates I can remove the formulas... The formula that AhoyNC gave breaks the back of the work...!
 
Upvote 0
maybe like this

Column11st date2nd date
1 (39)-50 (39), 1939 3 Sep-25 Sep (PDF, 106.611 MB)3 Sep 193925 Sep 1939
51 (39)-100 (39), 1939 21 Sep-27 Oct (PDF, 97.278 MB)21 Sep 193927 Oct 1939
101 (39)-150 (39), 1939 28 Oct-6 Dec (PDF, 78.001 MB)28 Oct 19396 Dec 1939
102 (39)-150 (39), 1939 28 Oct-6 Jan (PDF, 78.001 MB)28 Oct 19406 Jan 1940
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

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