Forum Discussion
Solved
Need to remove numbers from a column
I have a column that has a mix of numbers and text. Example:
Column
ABC
ABC2
123
456
A1B1
I need to remove the ones that have only numbers (123, 456) not the ones that have text and numbers (ABC2, A1B1).
I tried the custom column Text.Remove ([Column1], {"0".."9"} - however that also removes the numbers from the ones that have ABC2 and A1B1.
Any help would be greatly appreciated.
Use this in a custom column
if Value.FromText([Column1]) is number then null else [Column1]
4 Replies
jj101- Try this
if Text.Regex.IsMatch([Column], "^[0-9]+$") then null else [Column]Let me know if this works.
If this post helps to find solution would be happy if you could mark my post as a solution and give it a thumbs up
Best Regards
Manoj Nair
Linkedin - https://www.linkedin.com/in/manoj-nair-%E2%98%81-344666104/Use this in a custom column
if Value.FromText([Column1]) is number then null else [Column1]Vijay_A_Verma thank you this worked lilke a charm!
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVorVAdNGYIahkTGYNjE1g0gYOhkqxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column = _t]), #"Transformed Column" = Table.TransformColumns(Source, {"Column", each if (try Number.From(_))[HasError] then _ else null}) in #"Transformed Column"
