Skip to main content
Announcements
MAINTENANCE ALERT: Dec. 12th starting 9 AM CET. Community will be read-only. GET DETAILS
cancel
Showing results for
Search instead for
Did you mean:
Creator

## Phone numbers clean up

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

1 Solution

Accepted Solutions
Master III

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

7 Replies

May be this?

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Specialist

Hi,

=SubField(120888888.00 ,'.',1)

=SubField(MyField ,'.',1)

Master III

Maybe this

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

Creator
Author

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?

Creator
Author

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

Master III

Can You provide an example ?

Master III

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

Community Browser