I am currently working on cleaning up customer data on our database.
The issue is some phone numbers have '.00' at the end and I am finding it difficult to remove them.
The case to resolve is... if a phone number has '.00' - eg 120888888.00 then it should be converted to 0120888888.
My formula is given below:
0&Left(Text([Phone Number]),Len(Text([Phone Number]))-3),
Can someone help with this. My script is finding it difficult to differentiate between '.00' and '000'.
May be this?
=If(Right('120888888.00',2)='00', '0' & SubField('120888888.00', '.', 1))
Thank you for the prompt response.
Your formula works well when there is .00 involved. However when it is not the case eg phone number = 0288288888 then it changes it to 00288288888.
Is there a way to formulate this on both scenarios?