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?

Improve this question
1
  • 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, 2014 at 17:48

1 Answer 1

Reset to default
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)))
  )
)
Improve this answer
3
  • 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, 2014 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, 2014 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, 2014 at 21:49

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .