# New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Contributor

## 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
Honored Contributor III

## Re: Phone numbers clean up

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

7 Replies

## Re: Phone numbers clean up

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)
Valued Contributor

## Re: Phone numbers clean up

Hi,

=SubField(120888888.00 ,'.',1)

=SubField(MyField ,'.',1)

Honored Contributor III

## Re: Phone numbers clean up

Maybe this

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

Contributor

## Re: Phone numbers clean up

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?

Contributor

## Re: Phone numbers clean up

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

Honored Contributor III

## Re: Phone numbers clean up

Can You provide an example ?

Honored Contributor III

## Re: Phone numbers clean up

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