function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Ellen_eEllen_e 

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.

Best Answer chosen by Admin (Salesforce Developers) 
Shannon HaleShannon Hale

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:

 

IF(
  CONTAINS( Item_Name__c, "x" ), 
  MID( Item_Name__c, FIND( "x", Item_Name__c ) - 2, 5 ), 
  IF( 
    OR(
      CONTAINS( Item_Name__c, "100M" ), 
      CONTAINS( Item_Name__c, "100m" )
    ),
    "100m", 
    IF(
      CONTAINS( Item_Name__c, "1630" ), 
      "16x30", 
      IF(
        CONTAINS( Item_Name__c, "2040" ), 
        "20x40", 
        IF(
          CONTAINS( Item_Name__c, "3050" ), 
          "30x50", 
          IF(
            CONTAINS( Item_Name__c, "4070" ), 
            "40x70", 
            NULL
          )
        )
      )
    )
  )
)

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

Madhan Raja MMadhan Raja M

Hi Ellen,

 

Try this formula:

 

if(CONTAINS(Item_Name__c,'x'),   MID(Item_Name__c, FIND('x',Item_Name__c)-2,5),
if(CONTAINS(Item_Name__c,'100m'),'100m',
if(CONTAINS(Item_Name__c,'1630'),'16x30',
if(CONTAINS(Item_Name__c,'2040'),'20x40',
if(CONTAINS(Item_Name__c,'3050'),'30x50',
if(CONTAINS(Item_Name__c,'4070'),'40x70',''))))))

 

Click on KUDOS button if the post helps you!

If a reply to a post answers your question or resolves your problem, please mark it as the solution to the post so that others may benefit.

 

Regards,

Madhan Raja M
Shannon HaleShannon Hale

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:

 

IF(
  CONTAINS( Item_Name__c, "x" ), 
  MID( Item_Name__c, FIND( "x", Item_Name__c ) - 2, 5 ), 
  IF( 
    OR(
      CONTAINS( Item_Name__c, "100M" ), 
      CONTAINS( Item_Name__c, "100m" )
    ),
    "100m", 
    IF(
      CONTAINS( Item_Name__c, "1630" ), 
      "16x30", 
      IF(
        CONTAINS( Item_Name__c, "2040" ), 
        "20x40", 
        IF(
          CONTAINS( Item_Name__c, "3050" ), 
          "30x50", 
          IF(
            CONTAINS( Item_Name__c, "4070" ), 
            "40x70", 
            NULL
          )
        )
      )
    )
  )
)

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.

This was selected as the best answer
Ellen_eEllen_e

Thank you Madhan,

 

your reply was a big help!
Ellen_eEllen_e
Thank you very much Shannon!