Phone numbers clean up

[snehamahaveer]

Hi All,

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:

=if(Right(Text([Phone Number]),3)='.00',

0&Left(Text([Phone Number]),Len(Text([Phone Number]))-3),

Text([Phone Number]))

Can someone help with this. My script is finding it difficult to differentiate between '.00' and '000'.

Many thanks,

SM

[Anil_Babu_Samineni]

May be this?

=If(Right('120888888.00',2)='00', '0' & SubField('120888888.00', '.', 1))

[el_aprendiz111]

Hi,

=SubField(120888888.00 ,'.',1)

=SubField(MyField ,'.',1)

[antoniotiman]

Maybe this

=Num(SubField('120888888.00','.',1),'0000000000')

[snehamahaveer]

Hi Anil,

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?

[snehamahaveer]

Hi Antonio,

I guess your formula works for 99% of my customers. However, is there a way to keep the spaces between the phones if any are found.?

Thanks,

SM

[antoniotiman]

Can You provide an example ?

[sasiparupudi1 · Accepted solution]

if(index([Phone Number],'.')>0,0 &SubField([Phone Number], '.',1),[Phone Number])