Filter on specific text in text formatted line item

PrevContributor
PrevContributor
in Modeling

Hi, I'm trying to filter on a text formatted line item. The source of the text is from another module through the textlist formula. In the text formatted line item there are several descriptions of transactions and I need to filter some words out of the string and then use the result to predict descriptions for transactions where no description is given. I was making my own filter with the formulas LEFT, MID and FIND but it doesn't work the way I want to. It looks like Anaplan doesn't "see" the characters that I see in the line item. An example of the formula I used is below: IF LEFT('Text 1', 15) = "Kostenverdeling" OR LEFT('Text 1', 10) = "Registered" THEN MID('Text 1', FIND(", ", 'Text 1'), 100) ELSE 'Text 1' It seems like Anaplan doesn't recognise the words "Kostenverdeling" and "Registered" in the text string. Does anyone know a differnent solution for this problem or has experience with this issue? Thanks, Kind regards,  Stef

Tagged:

Answers

  • Jaakko
    Jaakko
    Hi Stef,

    What is the result of the formula you use? An empty string? If so, it might be that MID function returns an empty string because FIND function returns zero. I see that the formula is trying to find a comma and a space from Text 1. What is the separator that you use in your textlist function? Comma only or do you have the space also there?

    Kind regards,
    Jaakko 
  • peter_mcanena
    peter_mcanena
    The formula works for me - see attached screenshot.

    The explanation will be one of the following:
    • The text in 'Text 1' doesn't match "Kostenverdeling" or "Registered" exactly, either because there's a space or something at the start, or because there's a spelling mistake in the underlying list
    • Your formula is case-sensitive but "Kostenverdeling" is actually coming out as "KOSTVERDELING" or "kostenverdeling" etc. If you want a non-case-sensitive formula, use the COMPARE function instead
    • The second part of your formula is failing because there's no comma in 'Text 1'. If this isthe case you'll see empty strings in your result
    To diagnose the problem, add some temporary line items with formulae like
    IF LEFT('Text 1', 15) = "Kostenverdeling" THEN "K" ELSE IF LEFT('Text 1', 10) = "Registered" THEN "R" ELSE BLANK
    and
    MID('Text 1', FIND(", ", 'Text 1'), 100)
    This will help you work out which part of your formula is failing.

    Pete

Categories