2

I'm trying to create a formula field that returns a number. The source of the field is a text value such as the following examples.

  • 0.0.004.0

  • 1.24.004.0

  • 3.336.004.0

  • 4.672.002.0

The goal is to store the values in bold above in the formula number field. I believe this is possible with a couple text functions (Begins, Find, Left and Right) wrapped in a Value function to return the number represented by the text highlighted above.

Can you solve this?

  • Alot of hacking resulting in: VALUE(RIGHT(My_Field__c,(FIND(".", My_Field__c)+3)))... I think Alex's suggestion of using "MID" is the key here... thanks. – Steve Richardson Sep 29 '14 at 17:48
4

The following should do the job.

It finds the position of the first . in the string. Then finds the position of the first . in the rest of the string and uses MID and a slight offset to pick out the value from between the two positions.

VALUE(
  MID(
    YourField__c, 
    FIND(".", YourField__c) + 1, 
    FIND(".", RIGHT(YourField__c, LEN(YourField__c) - FIND(".", YourField__c)))
  )
)
  • Alex, this works perfectly. I tried adding a bit of error handling to prevent "#Error!". This can occurr for example with two consecutive "." chars. I modified the formula as follows: – Steve Richardson Sep 29 '14 at 17:59
  • I modified the formula as follows: IF(ISNUMBER(TEXT(VALUE( MID( App_Recovery_Priority_Sequence_Value__c, FIND(".", App_Recovery_Priority_Sequence_Value__c) + 1, FIND(".", RIGHT(App_Recovery_Priority_Sequence_Value__c, LEN(App_Recovery_Priority_Sequence_Value__c) - FIND(".", App_Recovery_Priority_Sequence_Value__c))) ) ))), VALUE( MID( App_Recovery_Priority_Sequence_Value__c, FIND(".", App_Recovery_Priority_Sequence_Value__c) + 1, FIND(".", RIGHT(App_Recovery_Priority_Sequence_Value__c, LEN(App_Recovery_Priority_Sequence_Value__c) - FIND(".", App_Recovery_Priority_Sequence_Value__c))) ) ),672) – Steve Richardson Sep 29 '14 at 18:01
  • I should clarify, the error handling is not working as expected. It is not a big concern but error handling for the formula returning #error! would be a nice add-on. – Steve Richardson Sep 29 '14 at 21:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.