Remove text-chunk with numbers or characters in them from a particular string

[vedantsri]

Hi,

For a project I was working on I was hoping to do the following:

I have a column which has data like this: 

#876 Amber Rudd
Amber Rudd
CC#876 Amber Rudd

I want to create a new column where I just have:

Amber Rudd
Amber Rudd
Amber Rudd

So basically, removing the chunk of the string which has a number/character in it. I'm not trying to remove just the numbers or characters from the string but rather the entire chunk in that string containing either number or character. 

Is there a way to do this via power query?

Thanks!

[AlexisOlson · Accepted solution]

There are many ways to do this. Here are five different examples:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUrYwN1NwzE1KLVIIKk1JUYrViVZC4zo7YyiKBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Text.SplitAny", each Text.Trim(List.Last(Text.SplitAny([Column1], "0123456789"))), type text),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Text.PositionOfAny", each Text.Trim(Text.RemoveRange([Column1], 0, 1 + Text.PositionOfAny([Column1], {"0".."9"}, Occurrence.Last))), type text),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "List.ContainsAny", each Text.Combine(List.Select(Text.Split([Column1], " "), each not List.ContainsAny(Text.ToList(_), {"0".."9"})), " "), type text),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "List.LastN", each Text.Trim(Text.Combine(List.LastN(Text.ToList([Column1]), each not List.Contains({"0".."9"}, _)))), type text),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Text.Select", each Text.Combine(List.Select(Text.Split([Column1], " "), each Text.Select(_, {"0".."9"}) = ""), " "), type text)
in
    #"Added Custom4"

Just the custom column code:

Text.Trim(List.Last(Text.SplitAny([Column1], "0123456789")))

Text.Trim(Text.RemoveRange([Column1], 0, 1 + Text.PositionOfAny([Column1], {"0".."9"}, Occurrence.Last)))

Text.Combine(List.Select(Text.Split([Column1], " "), each not List.ContainsAny(Text.ToList(_), {"0".."9"})), " ")

Text.Trim(Text.Combine(List.LastN(Text.ToList([Column1]), each not List.Contains({"0".."9"}, _))))

Text.Combine(List.Select(Text.Split([Column1], " "), each Text.Select(_, {"0".."9"}) = ""), " ")

[HotChilli]

With the sample provided you could convert it to a list with Text.Split([Column1], " ")

then create a column to check if there are 3 items (if so, drop the first one)

if List.Count([Custom]) = 3 then List.Skip([Custom],1) else [Custom]

then Extract the values using the column header.

---

If the real data is more complex, you could use Text.PositionOfAny(List.First([Custom]), {"0".."9"}) -I haven't tested this, just writing it freehand -  to check for a number in the first item in the list and then use similar logic to skip the first item 

[vedantsri]

Hi thank you for replying! I tried using text.split but it says text.split wasn't recognized. Does it not work in power query or am I doing something wrong?

[ronrsnfld]

M Code is case sensitive.  text.split is not the same as Text.Split