You need to sign in to do that
Don't have an account?
Custom Field Formula - Not Working
Hello!
I have a large amount of data that needs to be cleaned up through salesforce inorder to create reports. There are numbers in the following formats:
ABC##x##ABC, ABC-####-ABC, ABC100mABC, ABC100MABC
The ABC* represent text strings of varying lengths.
I would like to "pull out" the areas numbers/ text I idicated in blue in a seperate filed,
I have created a formula field as follows:
IF(CONTAINS("x", Item_Name__c), MID(Item_Name__c, FIND("x", Item_Name__c), 5), IF(CONTAINS("100M:100m", Item_Name__c), "100m", IF(CONTAINS("1630", Item_Name__c), "16x30", IF(CONTAINS("2040", Item_Name__c), "20x40", IF(CONTAINS("3050", Item_Name__c), "30x50", IF(CONTAINS("4070", Item_Name__c), "40x70", NULL))))))
When I uploaded the data however, the formula did not work and returned only "blank" values. I am worried it is too complex or incorrectly formatted. Any help or suggestions would be greatly appreciated!
Thank you.
Hi Ellen_e,
You had me baffled on this one until I realized the CONTAINS() arguments were backwards -- you need to have the string you want to look at as the first argument, and the comparison text as the second. So try this:
First, I switched the arguments around in all the CONTAINS() functions.
Second, I changed your "100M:100m" string to an OR() with separate checks.
Third, I subtracted 2 from the index of you MID() function so it gets the two numbers before the "x" instead of five characters starting with "x".
I tried it with some sample data in my test org, see how it works for your data. It might need some additional tweaks.
All Answers
Hi Ellen_e,
You had me baffled on this one until I realized the CONTAINS() arguments were backwards -- you need to have the string you want to look at as the first argument, and the comparison text as the second. So try this:
First, I switched the arguments around in all the CONTAINS() functions.
Second, I changed your "100M:100m" string to an OR() with separate checks.
Third, I subtracted 2 from the index of you MID() function so it gets the two numbers before the "x" instead of five characters starting with "x".
I tried it with some sample data in my test org, see how it works for your data. It might need some additional tweaks.
Thank you Madhan,
your reply was a big help!