Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Removing unexpected text from a column

Hello,

I have a column which contains a number of different word values that repeat numerous times down the column. 

Sometimes there's instances where the these words have extra text at the end and there's different delimiters used so its hard to predict and therefore I've not used that function. 

 

Is there a way to just retain the standard word values and delete the extra text without knowing what that extra text will be? It happens across the majority of the different word values too.

 

So in the below table I'd like to only keep the non-red text. Thanks

 

StatusCount
Backlog12
Backlog - 26th Sept45
Archived2
Archived xyz0
Archived-12314
  • Hi Anonymous 

     

    Why not try a custom column 

    if Text.Contains([Status], "Backlog") then "Backlog else
    if Text.Contains([Status], "Archived") then "Archived" else [Status]

     

    Thanks

    Joe

    If this post helps, then please Accept it as the solution

     

3 Replies

  • mussaenda's avatar
    mussaenda
    Community Champion

    Hi Anonymous 

    How are we going to know your standard words? are they only 2 words --Backlog and Archived?

     

  • JoeBarry's avatar
    JoeBarry
    Solution Sage

    Hi Anonymous 

     

    Why not try a custom column 

    if Text.Contains([Status], "Backlog") then "Backlog else
    if Text.Contains([Status], "Archived") then "Archived" else [Status]

     

    Thanks

    Joe

    If this post helps, then please Accept it as the solution

     

  • mlsx4's avatar
    mlsx4
    Memorable Member

    Hi Anonymous 

     

    If you only need the first word, you can extract text before delimiter, and use "space" as char