Need Date from text

[hardeep.kanwar]

Hi Experts

I need to extract Invoice number and Dates in two Columns i.e. Invoice Number and Dates (DD-MMM-YY) format.

I can use text to column but the problem is that every cell have different range or length.

Can you Please help on this.

[TABLE="width: 180"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Invoice Number[/TD]
[/TR]
[TR]
[TD]P-03/18-485 / 31-3-2018[/TD]
[/TR]
[TR]
[TD]P-03/18-486 / 31-3-2018[/TD]
[/TR]
[TR]
[TD]A-02-2018 / 4-4-2018[/TD]
[/TR]
[TR]
[TD]A-01-2018 / 4-4-2018[/TD]
[/TR]
[TR]
[TD]A-03-2018 / 4-4-2018[/TD]
[/TR]
[TR]
[TD]P-04/18-015 / 10-4-2018[/TD]
[/TR]
[TR]
[TD]P-04/18-016 / 10-4-2018[/TD]
[/TR]
[TR]
[TD]74/INC 2017-18 / 11-4-2018[/TD]
[/TR]
[TR]
[TD]18-19-1005 / 13-4-2018[/TD]
[/TR]
[TR]
[TD]18-19-1006 / 13-4-2018[/TD]
[/TR]
[TR]
[TD]SA/18-19/06 / 16-4-2018[/TD]
[/TR]
[TR]
[TD]916 / 29-3-2018[/TD]
[/TR]
[TR]
[TD]917 / 29-3-2018[/TD]
[/TR]
[TR]
[TD]918 / 29-3-2018[/TD]
[/TR]
[TR]
[TD]919 / 29-3-2018[/TD]
[/TR]
[TR]
[TD]LC GS 06/2018-2019 / 17-5-2018[/TD]
[/TR]
[TR]
[TD]LC GS 12/2018-2019 / 17-5-2018[/TD]
[/TR]
[TR]
[TD]DIP-2018-19-002 / 21-5-2018[/TD]
[/TR]
[TR]
[TD]BBMA/18-19/030 / 11-7-2018[/TD]
[/TR]
</tbody>[/TABLE]

 

[alansidman]

This is a simple column Split in Power Query. Here is the Mcode for that exercise

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Invoice Number", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Invoice Number", Splitter.SplitTextByEachDelimiter({"/"}, QuoteStyle.Csv, true), {"Invoice Number.1", "Invoice Number.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Invoice Number.1", type text}, {"Invoice Number.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Invoice Number.2", "DATE"}}),
    #"Trimmed Text" = Table.TransformColumns(#"Renamed Columns",{{"DATE", Text.Trim, type text}})
in
    #"Trimmed Text"

 

[sandy666]

first: there is Excel 2007
second: enough Split by " / " (space/space)

as example:

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    Split = Table.SplitColumn(Source, "Invoice Number", Splitter.SplitTextByDelimiter(" / ", QuoteStyle.Csv), {"Invoice Number", "Date"}),
    Type = Table.TransformColumnTypes(Split,{{"Invoice Number", type text}, {"Date", type date}})
in
    Type

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Invoice Number[/td][td=bgcolor:#70AD47]Date[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]P-03/18-485[/td][td=bgcolor:#E2EFDA]

31/03/2018​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]P-03/18-486[/td][td]

31/03/2018​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]A-02-2018[/td][td=bgcolor:#E2EFDA]

04/04/2018​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]A-01-2018[/td][td]

04/04/2018​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]A-03-2018[/td][td=bgcolor:#E2EFDA]

04/04/2018​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]P-04/18-015[/td][td]

10/04/2018​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]P-04/18-016[/td][td=bgcolor:#E2EFDA]

10/04/2018​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]74/INC 2017-18[/td][td]

11/04/2018​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]18-19-1005[/td][td=bgcolor:#E2EFDA]

13/04/2018​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]18-19-1006[/td][td]

13/04/2018​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]SA/18-19/06[/td][td=bgcolor:#E2EFDA]

16/04/2018​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]916[/td][td]

29/03/2018​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]917[/td][td=bgcolor:#E2EFDA]

29/03/2018​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]918[/td][td]

29/03/2018​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]919[/td][td=bgcolor:#E2EFDA]

29/03/2018​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]LC GS 06/2018-2019[/td][td]

17/05/2018​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]LC GS 12/2018-2019[/td][td=bgcolor:#E2EFDA]

17/05/2018​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]DIP-2018-19-002[/td][td]

21/05/2018​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]BBMA/18-19/030[/td][td=bgcolor:#E2EFDA]

11/07/2018​

[/td][/tr]
[/table]

 

[sandy666]

with formula:

[Table="width:, class:head"][tr=bgcolor:#000000][th] [/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

1​

[/td][td]Invoice number[/td][td]Date[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

2​

[/td][td]P-03/18-485[/td][td]31-3-2018[/td][td]F2:[/td][td]=LEFT(A2,SEARCH(" /",A2)-1)[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

3​

[/td][td]P-03/18-486[/td][td]31-3-2018[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

4​

[/td][td]A-02-2018[/td][td]4-4-2018[/td][td]G2:[/td][td]=MID(SUBSTITUTE(A2,F2,""),4,999)[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

5​

[/td][td]A-01-2018[/td][td]4-4-2018[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

6​

[/td][td]A-03-2018[/td][td]4-4-2018[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

7​

[/td][td]P-04/18-015[/td][td]10-4-2018[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

8​

[/td][td]P-04/18-016[/td][td]10-4-2018[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

9​

[/td][td]74/INC 2017-18[/td][td]11-4-2018[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

10​

[/td][td]18-19-1005[/td][td]13-4-2018[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

11​

[/td][td]18-19-1006[/td][td]13-4-2018[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

12​

[/td][td]SA/18-19/06[/td][td]16-4-2018[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

13​

[/td][td]916[/td][td]29-3-2018[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

14​

[/td][td]917[/td][td]29-3-2018[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

15​

[/td][td]918[/td][td]29-3-2018[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

16​

[/td][td]919[/td][td]29-3-2018[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

17​

[/td][td]LC GS 06/2018-2019[/td][td]17-5-2018[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

18​

[/td][td]LC GS 12/2018-2019[/td][td]17-5-2018[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

19​

[/td][td]DIP-2018-19-002[/td][td]21-5-2018[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

20​

[/td][td]BBMA/18-19/030[/td][td]11-7-2018[/td][td][/td][td][/td][/tr]
[/table]

 

[hardeep.kanwar]

Thanks for the Help sandy666 and alansidman

I have no idea about Power query but seems if very useful ,will try my best to learn this function

Sandy666 your formula help me lot

 

[alansidman]

Power Query. Look at this book. M is for (Data) Monkey by Ken Puls and Miguel Escobar. Very good learning tool.

 

[sandy666]

Thanks for the Help sandy666 and alansidman

Glad we could help

What is Power Query?

Have a nice day