How to set the script to get correct value in table box?

[#1]

I have data in table box for Number and Mobile.

Number

087-7092208
016267007
040466943
0860647091
0872761125
0282718
N/A
085 7041865
0047653866017203
01-2889533
n/a

Mobile

0876746764
087-76767768
087-089566536
0756457459
04746746767
03323338768
08868578768
089076646565
004765876776768

I need to replace where ever there is 0 it should replace with '4765' i,e if the value is 0876746764 it should get output as 4765876746764. If the value is like this 004765876776768 it should delete '00' and keep only 4765876776768

when I tried below in script it is showing data as 476547654765876776768 i,e values are entering twice or the 0 is not dropping.

and where ever the value is n/a it should keep as n/a

How to change below script. Please can anyone suggest me.

[Number],
     '4765'&Replace(LTrim(Replace(KeepChar([Number], '0123456789'),'0',' ')),' ','0')  as  [Number_1],
     Mobile,
     '4765'&Replace(LTrim(Replace(KeepChar(Mobile, '0123456789'),'0',' ')),' ','0')  as [Mobile_1]

Thanks.

[#2]

Any help please

[rubenmarin]

Hi Amelia, hope this helps:

If(Left(KeepChar(Number, '123456789'), 4)=4765, Mid(Number, index(Number, '4765')),

If(Left(Number,1)=0, '4765' & Mid(Number, 2), Number)

)

[tresesco]

May be like:

'4765'&Replace(Left([Number],2),'0','') & KeepChar(Mid([Number],3), '0123456789')  as  [Number_1],

[Anonymous]

Hi Amelia

try this script

Attached is the application

Assuming Number is your Field and chainge 'Zero' to respective number

Test:

LOAD text(Number) as Number

FROM

C:\Desktop\DimensionMme.xlsx

(ooxml, embedded labels, table is Sheet1);

for i = 1 to NoOfRows('Test')

  let number=FieldValue('Number',$(i));

  for j= 1 to len('$(number)')

  if(left('$(number)',$(j))>0)then

  if($(j)=1)then

  let final_number = '$(number)';

  exit for

  ELSE

  let final_number=Replace('$(number)', left('$(number)',$(j)-1),'Zero');

  exit For

  end if

  end if

  next

  if($(i)=1)then

  Final:

  load

  '$(final_number)' as final_number

  Resident

  Test;

  else

  Concatenate(Final)

  LOAD

  '$(final_number)' as final_number

  Resident

  Test;

  ENDIF

next

Regards

Harsha

[PrashantSangle]

Hi,

Try like

if(wildmatch(number,'00*'),replace(number,'00',''),

     if(wildmatch(number,'0*'),replace(number,'0','4765'),number)

)

Regards

[#7]

Thanks. It is working. one more request please. In the same data how can I remove in between spaces, bracets, '-'

Please suggest me.

476587-73539223538

476585 735341865

4765 (0) 872686352

[jerrysvensson]

purgechar()

[PrashantSangle]

Hi,

As Jerry suggested you can use Purgechar() or

you can try with keepchar() also.

Like Keepchar(FieldName,'0123456789')

Regards

[#10]

Thanks. when  am using this it is going again back to original values. How can I include Keepchar(FieldName,'0123456789')    in to below expression

if(wildmatch(number,'00*'),replace(number,'00',''),

     if(wildmatch(number,'0*'),replace(number,'0','4765'),number)

)

Please suggest me.