• 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.

Need help with formula

mithil1

Member
In column A I have text like mentioned below. I am trying to separate the City Name and date in two separate columns. I don't need the other text.

Alameda 1-10-20 CSV-PAY SUMMARY RUN.csv
Palm Harbor 1-15-20 Hourly CSV-PAY SUMMARY RUN.csv
Palm Harbor 1-15-20 Salary CSV-PAY SUMMARY RUN.csv


Basically, there are various combinations like above and there are different city names. I tried using mid, search, left, right, but its not helping.

Will appreciate help.

~M
 

AlanSidman

Well-Known Member
Using Power Query I split the columns Non-Digit to Digit
Split again Digit to Non Digit
Merged the numerical data
Deleted the non-essential columns

Here is the Mcode for those steps

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Character Transition" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Column1.1", "Column1.2", "Column1.3", "Column1.4"}),
    #"Split Column by Character Transition1" = Table.SplitColumn(#"Split Column by Character Transition", "Column1.4", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Column1.4.1", "Column1.4.2"}),
    #"Merged Columns" = Table.CombineColumns(#"Split Column by Character Transition1",{"Column1.2", "Column1.3", "Column1.4.1"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Date"),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Column1.4.2"})
in
    #"Removed Columns"
 

Attachments

mithil1

Member
Thanks Alan! I have not used power query formulas before. Is there a way we can just use formula in excel? I will try though.

I really thank you for taking time out and responding.

Mi
 

AlanSidman

Well-Known Member
Not in my skill bank. If you are interested in learning about PQ, then click on the link in my signature.
 
Top