Forum Discussion

bandaid's avatar
bandaid
New Member
5 years ago
Solved

split by non ascii characters

Hi I have a row that contains ascii and non-ascii characters.  eg: Tokyo 東京, 大阪Osaka   The entry is not always separated by spaces. Is there anyway to split the columns with words that start(cont...
Need Help
  • HotChilli's avatar
    HotChilli
    5 years ago

    Can you try these 2 lines please (in Power Query).  Add them in the Advanced Editor. I think I've got the syntax correct but if you could test them that would be great.  You might need to rename the parts that refer to previous steps or column names.

    #"Split Column by Character Transition" = Table.SplitColumn(#"Renamed Columns", "Column1", Splitter.SplitTextByCharacterTransition({"A".."z"}, (c) => not List.Contains({"A".."z"}, c)), {"Column1.1", "Column1.2"}),
    #"Split Column by Character Transition1" = Table.SplitColumn(#"Split Column by Character Transition", "Column1.1", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"A".."z"}, c), {"A".."z"}), {"Column1.1.1", "Column1.1.2"})
in
    #"Split Column by Character Transition1"

    Essentially it's performing two splits on the same column. One from english alphabet to non-english alphabet and the 2nd from non-english alphabet to english alphabet.

    Let me know how you get on.

HotChilli's avatar
HotChilli
Community Champion
5 years ago

Can you try these 2 lines please (in Power Query).  Add them in the Advanced Editor. I think I've got the syntax correct but if you could test them that would be great.  You might need to rename the parts that refer to previous steps or column names.

#"Split Column by Character Transition" = Table.SplitColumn(#"Renamed Columns", "Column1", Splitter.SplitTextByCharacterTransition({"A".."z"}, (c) => not List.Contains({"A".."z"}, c)), {"Column1.1", "Column1.2"}),
    #"Split Column by Character Transition1" = Table.SplitColumn(#"Split Column by Character Transition", "Column1.1", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"A".."z"}, c), {"A".."z"}), {"Column1.1.1", "Column1.1.2"})
in
    #"Split Column by Character Transition1"

Essentially it's performing two splits on the same column. One from english alphabet to non-english alphabet and the 2nd from non-english alphabet to english alphabet.

Let me know how you get on.

  • bandaid's avatar
    bandaid
    New Member
    5 years ago

    Thank you! You made my day! 🙂 

    The actual data had special charactes such as apostrophes which were getting recognized as non-ascii but I have a better understanding of how to use powerquery to split the data. Thank you again!