Forum Discussion
Impactful IndividualSplit a column in letters and numbers
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.
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.
5 Replies
v-kpoloju-msftCommunity Support
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.- jennratten
Super User
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 - mdaatifraza5556
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.
- 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"
