Forum Discussion

JonasN's avatar
JonasN
Frequent Visitor
2 years ago
Solved

Trim/remove text with a measure

Hi!

 

I have a column that contains a text with and a combination of numbers in parentheses "<text> (<4 digits>-<3 digits>)", I want in a measure to remove the parenthesis (including the value in between), can I use regular expressions?
The value in the parentheses is an enumerator, and I want to remove it to be able to group on the column.

 

/Jonas

 

Need Help
  • rajendraongole1's avatar
    rajendraongole1
    2 years ago

    Hi JonasN - you can create a calculated column use the above script , it works.

     

    Script sharing Again FYR:

    CleanedText =
    VAR OriginalText = Splcp[ColumnWithText]
    VAR StartPosition = FIND("(", OriginalText, 1, LEN(OriginalText))
    VAR EndPosition = FIND(")", OriginalText, 1, LEN(OriginalText))
    VAR TextBeforeParenthesis = LEFT(OriginalText, StartPosition - 1)
    VAR TextAfterParenthesis = MID(OriginalText, EndPosition + 1, LEN(OriginalText) - EndPosition)
    RETURN TRIM(TextBeforeParenthesis & TextAfterParenthesis)

     

     

     

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!

3 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    2 years ago

    HI JonasN - I have created data with some sample  with parentheses,got the below output on cleanedTextMeasure in visual.

     

     

    Measure:

    CleanedTextMeasure =
    VAR OriginalText = SELECTEDVALUE('

    splcp'[ColumnWithText])

    VAR StartPos = SEARCH("(", OriginalText, 1, LEN(OriginalText))
    VAR EndPos = SEARCH(")", OriginalText, 1, LEN(OriginalText))
    RETURN
    IF(
    StartPos > 0 && EndPos > 0,
    TRIM(LEFT(OriginalText, StartPos - 1) & RIGHT(OriginalText, LEN(OriginalText) - EndPos)),
    OriginalText
    )

    try the above logic and let know.

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!

  • JonasN's avatar
    JonasN
    Frequent Visitor
    2 years ago

    Just noticed that you can't use a measure in a Slicer, how do you solve that?

    • rajendraongole1's avatar
      rajendraongole1
      Icon for Super User rankSuper User
      2 years ago

      Hi JonasN - you can create a calculated column use the above script , it works.

       

      Script sharing Again FYR:

      CleanedText =
      VAR OriginalText = Splcp[ColumnWithText]
      VAR StartPosition = FIND("(", OriginalText, 1, LEN(OriginalText))
      VAR EndPosition = FIND(")", OriginalText, 1, LEN(OriginalText))
      VAR TextBeforeParenthesis = LEFT(OriginalText, StartPosition - 1)
      VAR TextAfterParenthesis = MID(OriginalText, EndPosition + 1, LEN(OriginalText) - EndPosition)
      RETURN TRIM(TextBeforeParenthesis & TextAfterParenthesis)

       

       

       

       

      Did I answer your question? Mark my post as a solution! This will help others on the forum!
      Appreciate your Kudos!!