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?
Your formulas appear to be working fine, I added the 3 fields to my dev org and tested out your formula and it worked fine, one thing I noticed is that you have the field Summary without the "__c" but I'm guessing you just missed that when asking the question. Attached is a screenshot of the result:
Edit: I didn't look closely at the formula but I now see the problem and have a corrected formula below:
(HYPERLINK("url/"&LEFT(jjb__Summary__c,15),LEFT(jjb__Summary1__c,FIND(",", jjb__Summary1__c)-1)))&
(HYPERLINK("url/"&RIGHT(jjb__Summary__c,15),RIGHT(jjb__Summary1__c,FIND(",", jjb__Summary1__c)+1)))&
(HYPERLINK("url/"&MID(jjb__Summary__c,17,15),MID(jjb__Summary1__c, FIND(",", jjb__Summary1__c),
FIND(",", jjb__Summary1__c, FIND(",", jjb__Summary1__c)+1)- FIND(",", jjb__Summary1__c))))
The 3rd parameter of the FIND is which character to start the search at within the string so in order to get the middle I used the second comma and subtracted the location of the first comma to get the number of characters to retrieve from the middle. You previously had it hardcoded to 8 characters.
You can take help from SUBSTITUTE(text, old_text, new_text) function . for example
TRIM( SUBSTITUTE(Target Kindergarten 1, Target, '')); it will give you
Kindergarten 1 This code will only work if you have fixed text value Target.
Best Answer
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 usesMIDand a slight offset to pick out the value from between the two positions.