0

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
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.

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.