Forum Discussion
Split a column in letters and numbers
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.
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