Skip to main content
Announcements
Customer Spotlight: Discover what’s possible with embedded analytics Oct. 16 at 10:00 AM ET: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
snehamahaveer
Creator
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
sasiparupudi1
Master III
Master III

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

View solution in original post

7 Replies
Anil_Babu_Samineni

May be this?

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
el_aprendiz111
Specialist
Specialist

Hi,

=SubField(120888888.00 ,'.',1)

=SubField(MyField ,'.',1)

antoniotiman
Master III
Master III

Maybe this

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

snehamahaveer
Creator
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?

snehamahaveer
Creator
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

antoniotiman
Master III
Master III

Can You provide an example ?

sasiparupudi1
Master III
Master III

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