How to compare and find single value from another cell containing multiple values separated by coma?

31MB · 2025-06-04T08:46:20+00:00

There was an error rendering this rich post.

31MB

Hello Anaplanners,

I have a scenario where I need to compare and find one value from a set of values present in another single cell.

Let me explain with demo modules

Source Module - Veggies and Vendors

Target Module - Veggies and Vendors Target

Here in the Target module, while using the given formula I am able to validate items present in Vendor column against the vendor column in Source module, the check box against Potato is not checked since there are multiple values for potato in source module but only one value for it in Target module.

So, I want a formula in Validation check line item so that even if one value is present against list item in vendor cell from among the many values in vendor cell in Source module, it should check. Please assist with this.

Accepted answers

Prajjwal88

Hi @MayurBodade ,

For this, you can simply use the FIND() function. Since both your Source and Target modules share the same dimensions, and the Vendor line items are text-formatted, you just need to check whether the vendor text in the Target exists within the vendor text in the Source.

You can use the following formula in your Validation Check line item:

FIND(Vendor, 'Veggies & Vendors'.Vendor) > 0

This will return TRUE if the vendor name from the Target is found anywhere in the corresponding Source cell.

Hope this helps!

Prajjwal88

Hi @31MB ,

This can be done by breaking the Input Value into the digits before and after the decimal point.

Assuming your input values are text-formatted (which seems to be the case based on your screenshot), you can use LEFT() and RIGHT() functions to extract the parts.
If your values are number-formatted, you can first convert them to text using the TEXT() function.

Here’s a suggested approach:

Split the Input Value into two parts using the FIND() function to locate the decimal.
Use LEFT() to get the portion before the decimal.
Use RIGHT() (along with LENGTH() and FIND() to get the portion after the decimal).
Finally, in the Validation Check line item, use a condition like:
LENGTH(PartBeforeDecimal) >= 5 AND LENGTH(PartAfterDecimal) >= 2

Please refer to the attached screenshots for the formulas and example logic.

Hope this Helps! :)

All comments

Prajjwal88

Hi @MayurBodade ,

You can use the following formula in your Validation Check line item:

FIND(Vendor, 'Veggies & Vendors'.Vendor) > 0

This will return TRUE if the vendor name from the Target is found anywhere in the corresponding Source cell.

Hope this helps!

31MB

Thanks @Prajjwal88 . This answers my question.

I have one more question for a different module.

In this module I have line item 'Input Value' which will have number values. In the validation line item, I have to ensure that only those boxes are check where the number in Input Value cell has >= 5 digits before decimal and >=2 digits after decimal. How to achieve this?

here's pic of the module

Thanks in advance

Prajjwal88

Hi @31MB ,

This can be done by breaking the Input Value into the digits before and after the decimal point.

Here’s a suggested approach:

Split the Input Value into two parts using the FIND() function to locate the decimal.
Use LEFT() to get the portion before the decimal.
Use RIGHT() (along with LENGTH() and FIND() to get the portion after the decimal).
Finally, in the Validation Check line item, use a condition like:
LENGTH(PartBeforeDecimal) >= 5 AND LENGTH(PartAfterDecimal) >= 2

Please refer to the attached screenshots for the formulas and example logic.

Hope this Helps! :)

31MB

Thanks @Prajjwal88 You're a genius.

Quick Links

Can you help?

Unanswered questions

Request for Workflow Design: End-User Report Finalization & Stakeholder Notification with Version Co
Hi everyone, I'm looking to design a workflow and would appreciate your input or guidance on how to set this up effectively in Anaplan. So, the goal is: Once end users complete their data input, they should be able to trigger a process that: Notifies stakeholders via email with a link to a report. The report includes: A…
OAUTH 2.0 vs Google Sheets Extension
Hi, My org is looking to explore leveraging Google Apps script to extract data out of Anaplan and update already built templates in Google Sheets. We use the Anaplan Extension currently but find it crashes often with the amount of tabs, data, etc. Has anyone used both Anaplan OAUTH 2.0 API and Goolge Apps Script to write…
Exports with anaplan using informatica connector
I'm trying to execute an Action to export data with the use of a Mapping in Informatica. But it gives us null rows .If we export directly from Anaplan , the export file has all the rows filled. Both exports have same number of rows but in the informatica export the values appear as blank. This issue is with only some…