• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Removing of date from text

ridwanr

New Member
Hi all,

Is there any formula in excel to remove the whole date month and year from the text if there is no parameters in it?
 

Attachments

  • Ridwan example2.xlsx
    8.4 KB · Views: 6
Here is a Power Query Solution

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Column1.1", "Column1.2"}),
    #"Split Column by Position" = Table.SplitColumn(#"Split Column by Character Transition", "Column1.2", Splitter.SplitTextByRepeatedLengths(2), {"Column1.2.1", "Column1.2.2", "Column1.2.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Column1.1", type text}, {"Column1.2.1", Int64.Type}, {"Column1.2.2", Int64.Type}, {"Column1.2.3", Int64.Type}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type1", {{"Column1.2.1", type text}, {"Column1.2.2", type text}, {"Column1.2.3", type text}}, "en-US"),{"Column1.2.1", "Column1.2.2", "Column1.2.3"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"New Date"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Merged Columns",{{"New Date", type date}})
in
    #"Changed Type2"

Data Range
A
B
1
Column1.1​
New Date​
2
Rooney​
5/10/2046​
3
Rav​
3/9/2016​
4
Sameerthegreat​
12/8/1999​
 

Attachments

  • Ridwan example2.xlsx
    24.3 KB · Views: 3
Well am using Office Professional 2019 and when launching the power query am unable to add the formula. Cannot select anything in the poweer query any idea about how I can make use of it.

Pls note that its my first experience with power query
 
I have been able to launch the power query and now where to insert the code in order to remove the date?
 
Here's another method using formula.

For string:
=SUBSTITUTE(A1,RIGHT(A1,6),"")

For date: Assuming English(US) as system date setting.
=TEXT(RIGHT(A1,6),"00-00-00")+0
 
In the PQ editor, click on View, Advanced Editor and paste the code over any existing. Suggest you look at the link in my signature also
 
Interesting thread to sharpen ones' PQ skills.

If splitting the text from numbers is enough, the PQ formulas are:
- Text.Remove ( [Text] , { "0".."9" } )
- Text.Select ( [Text] , { "0".."9" } )

Now converting that string 051046 to date can be done with:
- Text.Format ( "#{0}/#{1}/#{2} " , { Text.Range ( [Numbers] , 0 , 2 ) , Text.Range ( [Numbers] , 2 , 2 ) , Text.Range ( [Numbers] , 4 , 2 ) } )

I was intrigued by the fact the date was in the future, the year 2046. Where I assumed it was birth dates. My final PQ solution became therefore a bit more complicated that the one I started with.
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content]

    , MaxYr = Date.Year(Date.From(DateTime.LocalNow()))
   
    , GetName = Table.AddColumn(Source, "Name"
                        , each Text.Remove([Column1], {"0".."9"}))
   
    , GetNumbers = Table.AddColumn(GetName, "Numbers"
                        , each Text.Select([Column1], {"0".."9"}))
   
    , NumbersAsDate = Table.AddColumn(GetNumbers, "TempDate", each Text.Format("#{0}/#{1}/#{2}"
                                    , {
                                        Text.Range([Numbers], 0, 2)
                                      , Text.Range([Numbers], 2, 2)
                                      , Text.Range([Numbers], 4, 2)
                                      }
                                     
                                    ))
    , ChangeWithLocale = Table.TransformColumnTypes(NumbersAsDate, {{"TempDate", type date}}, "en-BE")
   
    , GetDate = Table.AddColumn(ChangeWithLocale, "Date"
                        , each if Date.Year([TempDate]) > MaxYr
                                then Date.AddYears([TempDate], -100)
                                 else [TempDate], Date.Type
                                 )

    , KeepNameDate = Table.RemoveColumns(GetDate,{"Column1", "Numbers", "TempDate"})

in
    KeepNameDate
 

Attachments

  • Copy of Ridwan example2.xlsx
    17 KB · Views: 3
Last edited:
Here is a Power Query Solution

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Column1.1", "Column1.2"}),
    #"Split Column by Position" = Table.SplitColumn(#"Split Column by Character Transition", "Column1.2", Splitter.SplitTextByRepeatedLengths(2), {"Column1.2.1", "Column1.2.2", "Column1.2.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Column1.1", type text}, {"Column1.2.1", Int64.Type}, {"Column1.2.2", Int64.Type}, {"Column1.2.3", Int64.Type}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type1", {{"Column1.2.1", type text}, {"Column1.2.2", type text}, {"Column1.2.3", type text}}, "en-US"),{"Column1.2.1", "Column1.2.2", "Column1.2.3"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"New Date"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Merged Columns",{{"New Date", type date}})
in
    #"Changed Type2"

Data Range
A
B
1
Column1.1​
New Date​
2
Rooney​
5/10/2046​
3
Rav​
3/9/2016​
4
Sameerthegreat​
12/8/1999​
Thanks you very much its work as per your instruction. :)
 
Just another question concerning the date removing the whole date month and year from the text if there is no parameters in it. Should I use the same formula as provided or it change completely?

Pls: See attached e.g for better understanding.

Thank you in advance
Ridwan
 

Attachments

  • Ridwan example1.xlsx
    179.1 KB · Views: 2
The date part of the strings appear appear to contain a valid separator , "/", so it is only necessary to extract the rightmost 10 digits and convert to a number
= DATEVALUE( RIGHT(string,10) )
The corresponding name are given by
= LEFT( string, LEN(string)-10 )
BTW. Please ensure the export sheet does not contain real data.
 
Back
Top