Filter on specific text in text formatted line item

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

Accepted answers

All comments

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

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

Text_Formula_1067.jpg

Quick Links

Can you help?

Unanswered questions

Azure blob connection Cloudworks vs ADO
I am trying to connect to Azure blob, the credentials work on cloudworks, but do not work on ADO. Has anyone else experienced this issue?
Berapa nomor Tokopedia?
Tokopedia Menyediakan layanan WhatsApp? Untuk menghubungi layanan Tokopedia melalui (WA)62_08174_9080_16 kamu bisa mengikuti.
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…