How to remove space and bracket in numbers?

[#1]

Hi,

I have phone number as below

00353 (0) 87 1706453

I can remove leading zeroes by using below function.

Replace(Replace(Ltrim(replace(replace(replace(Number,' ','_'),0,' '),'+',' ')),' ',0),'_',' ') as Number_1

can anyone help me how to remove spaces and brackets

how to replace 4765 in place of 0 which is in first place for below number

0504634545

Thanks.

[#2]

Thanks.

How to use column name in same formula to replace all column values? please suggest me

[MarcoWedel]

='4765'&Replace(LTrim(Replace(KeepChar(FieldName, '0123456789'),'0',' ')),' ','0')

[#4]

Thanks very much.

there are some n/a, N/A , none values in column data. when I applied the formula it is showing 4765 in place of n/a, N/A , none. How to set this so that in place of n/a, N/A , none how to show blanks

input	output
n/a	4765
N/A	4765
none	4765
none	4765

Please suggest.

[stevedark]

Take the expression you have and place it in an IF statement, checking N as the first character:

=if(lower(left(input, 1) = 'n', '', '4765'&Replace(LTrim(Replace(KeepChar(input, '0123456789'),'0',' ')),' ','0'))

Steve

[MarcoWedel]

Hi,

one solution could be:

tabPhoneNo:
LOAD If(not WildMatch(phoneNo, 'n/a','none'),Text('4765'&Replace(LTrim(Replace(KeepChar(phoneNo, '0123456789'),'0',' ')),' ','0'))) as phoneNo,
    RecNo() as ID 
INLINE [
    phoneNo
    NONE
    00353 (0) 87 1706453
    None
    003530871706452
    n/a
    ++353 (0) 87 1706451
    N/A
    +353 (0) 87 1706450
    087-1706450
    none
];

hope this helps

regards

Marco

[agomes1971]

Hi,

is this issue solved?

If not here it goes my suggestion:

To remove spaces

Replace('00353 (0) 87 1706453',' ','')

To replace 0 to 4765

replace('0504634545','0','4765')

Please mark the correct answer to close this issue.

HTH

André Gomes