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

  • PQ Split.xlsx
    25.3 KB · Views: 0
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
 
Not in my skill bank. If you are interested in learning about PQ, then click on the link in my signature.
 
Back
Top