replace first 2 letters only

**nasser** · 10-07-2011, 09:49 AM

mobile company changed the numbers from 012xxxxxxxxx to 0122xxxxxxxxx and from 018xxxxxxxxx to 0128xxxxxxxxx etc.
is there a formula or VBA code to make these changes
and i have to tell that
1- the numbers start by 0
2- SOME numbers start by + or 00

which mean i want to just change the specific 2 numbers to the new 3 numbers

i know it's possible ,but is it secure or may make a mess ?

**tom1977** · 10-07-2011, 09:55 AM

try to use function LEFT and REPLACE

regards

**Marcol** · 10-07-2011, 10:02 AM

Without a sample of your workbook it is hard to say.
However try this assuming your numbers are in A1 down

Please Login or Register  to view this content.

Drag/Fill Down

**nasser** · 10-07-2011, 10:05 AM

how to combine left and replace with IF because i have different numbers as you see , there is 012 and 018 and 016 etc...

**nasser** · 10-07-2011, 10:15 AM

012345667 must change to 0122345667
018323878 must change to 0128323878
016398989 must change to 0126398989
015323233 must change to 0105323233
012327773 must change to 0122327773

+9614545454 remain the same
00143431234 remain the same

**Marcol** · 10-07-2011, 10:39 AM

Try

Please Login or Register  to view this content.

Is this correct?

015323233 must change to 0105323233

**nasser** · 10-07-2011, 10:55 AM

thank you all
@ Marcol it works very well Thank you

**nasser** · 10-09-2011, 06:26 AM

as i said it works so well but i was have to change more than one code number so it will be like this

=IF(LEFT(A1,3)="012",SUBSTITUTE(A1,"012","0122",1),IF(LEFT(A1,3)="017",SUBSTITUTE(A1,"017","0127",1),IF(LEFT(A1,3)="018",SUBSTITUTE(A1,"018","0128",1),IF(LEFT(A1,4)="0150",SUBSTITUTE(A1,"0150","0150",1),IF(LEFT(A1,3)="011",SUBSTITUTE(A1,"011","0111",1),IF(LEFT(A1,3)="014",SUBSTITUTE(A1,"014","0114",1),IF(LEFT(A1,4)="0152",SUBSTITUTE(A1,"0152","0112",1),IF(LEFT(A1,3)="010",SUBSTITUTE(A1,"010","0100",1),IF(LEFT(A1,3)="016",SUBSTITUTE(A1,"016","0106",1),IF(LEFT(A1,3)="019",SUBSTITUTE(A1,"019","0109",1),IF(LEFT(A1,4)="0151",SUBSTITUTE(A1,"0151","0101",1),A1)))))))))))

thank you so much again