Split a column in letters and numbers

[ThomasWeppler]

Hi Power BI community

I am trying to split a coulumn in numnbes and letters. withn this code.
= Table.SplitColumn(#"Rename Repetitions", "Specifications", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Specifications.1", "Specifications.2"})
If their are any numbers in specification they will be at the start and the column will split as it should. But sometimes their are no numbers in specification and all the letters end up in Specifications.1.

All help is greatly appreciated.

[PwerQueryKees]

Hey @ThomasWeppler , the solutions provided with Text.Select will work, unless you have multiple sections of numbers and letter (like aaa123bbb). So beware...

[mdaatifraza5556]

This will also work, and the result will be like 
for num col 123 and text col "aaabbb"

[v-kpoloju-msft · Accepted solution]

Hi @ThomasWeppler,

Thank you for reaching out to the Microsoft fabric community forum.

I would like to acknowledge and thank both super users @mdaatifraza5556, and @jennratten, for their valuable responses. They have provided reliable solutions using Text.Select, which is an effective and robust method for extracting numeric and alphabetic characters, even when numbers are absent in the strin

To summarize:

• Text.Select([Specifications], {"0".."9"}) extracts only digits.
• Text.Select([Specifications], {"A".."Z", "a".."z"}) extracts only letters.

These methods guarantee consistent results regardless of the string's structure.

Additionally, if you need to extract leading numbers (i.e., numbers only at the start of the string), you might consider using a regular expression approach with Text.RegexMatch and Text.RegexReplace.

kindly please refer to the below following links:
Number.FromText - PowerQuery M | Microsoft Learn
Text.Select - PowerQuery M | Microsoft Learn
Splitter.SplitTextByCharacterTransition - PowerQuery M | Microsoft Learn

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[mdaatifraza5556]

Hi @ThomasWeppler 

Can you please try the steps below:

-----  Create two custom col using the below M code

1.   Text.Select([Specifications], {"0".."9"})

2.   Text.Select([Specifications], {"A".."Z","a".."z"})

If this answers your questions, kindly accept it as a solution and give kudos.

[jennratten]

Hello @ThomasWeppler - thanks for posting in the Fabric Community!  This is how you can split the numbers and letters.... Please let me know if you have any other questions.

let
    // Load the sample dataset
    Source = Table.FromRecords({
        [MixedColumn = "123ABC"],
        [MixedColumn = "XYZ"],
        [MixedColumn = "456DEF"],
        [MixedColumn = "GHI"],
        [MixedColumn = "789JKL"]
    }),
    // Add a column to extract numbers
    ExtractNumbers = Table.AddColumn(Source, "Numbers", each Number.FromText(Text.Select([MixedColumn], {"0".."9"})), Int64.Type),
    // Add a column to extract letters
    ExtractLetters = Table.AddColumn(ExtractNumbers, "Letters", each Text.Select([MixedColumn], {"A".."Z", "a".."z"}), type text)
in
    ExtractLetters