I have a field Title with value like below
Title=3.10-gotomeeting
I want a calculate formule to remove ("-")and (".") so that the output should be only a number i.e "310".
Can any one help me on this ..I have achieved this by using 2 calculated formules like below. but i want it in one.
order is the name of calucltaed column.
=LEFT([Title],SEARCH("-",[Title])-1) porder is the name of another calculated column
=IF(ISERR(SEARCH(".",order,1)) ,order ,REPLACE(order,SEARCH(".",order,1),1,"") )+0 After Digging in to it i finally made it .
=LEFT(REPLACE([Title],SEARCH(".",[Title]),1,"") ,SEARCH("-",REPLACE([Title],SEARCH(".",[Title]),1,"") )-1)+0 Explanation:
REPLACE([Title],SEARCH(".",[Title]),1,"")
This replaces (in the value of the "Title" field), starting at the position of the first occurance of the "." character, for a length of 1 (replacing 1 character only), with nothing (empty quotes). This gets rid of the first dot.
To get the rest of "-"
LEFT([Title],SEARCH("-",[Title])-1) We need to pass the above replace formule in place of title
So, what it comes down to is that in order to replace all occurances of a given character within a string, we need to know how many times it appears then nest the replacement logic that many times.
Hopefully this clears things up some.