Creating the same number format in a field

[#1]

Hi everyone

Please help, i've got a field with all cell fone numbers which i'm bringing in from an excel sheet, but they are some have different formats eg. one number will be formatted: 0117836431 (which is the format i want) and others would be formatted: 011-2345765, 011 365 2365, 011543 7896 etc. In the script, how do i make them all the same format??

[biester]

Hi,

have you tried loading using text(), e.g. text(cell_fone_number)?

Rgds,
Joachim

[#3]

Ok i've just tried text(cellnumber) as cellnumber in the script, reloaded it and still get brackets and commas etc in that field, i want to try and get all the numbers to look the same...

[biester]

Hi,

then I'd additionally work with MapSubstring (see reference manual). You can list the unwanted characters and the replace string (can be blank or null) there. Thus you could eliminate brackets, commas or whatsoever.

Rgds,
Joachim

[#5]

Ok is there not something a bit simpler to use like the text function u mentioned??

[biester]

Yes, of course, you could edit the excel sheet 😉

Rgds,
Joachim

[#7 · Accepted solution]

dude, use purgechar function...1st to remove '-' & then to remove blank spaces.

[#8]

Haha, thast what i'm trying to avoid in order for it to be automated when reloaded if u know what i mean, ok what is the syntax i would use when using mapsubstring, it is a rather large field...

[biester]

varun is right, you could also use purgechar, meaning:

PurgeChar('123-456(789)' , '-()' )

would result in

123456789.

Nevertheless, if you like I can also build a demo using MapSubstring.

Rgds,
Joachim

[#10]

So i'd put

purgechar('CellPhoneNo',''-()',')()',(()',' ()',',()'') as CellPhoneNo

To get rid of all the unneccesary dashes and spaces??

Thank you very much