• Status: Solved
• Priority: Medium
• Security: Public
• Views: 368

# Changing part of cells values in Excel

In Egypt all mobile operators will change their code adding a number as follows:
012 XXX xxxx ---> 0122 XXX xxxx
017 XXX xxxx ---> 0127 XXX xxxx
018 XXX xxxx ---> 0128 XXX xxxx
0150 XXX xxxx ---> 0120 XXX xxxx

010 XXX xxxx ---> 0100 XXX xxxx
016 XXX xxxx ---> 0106 XXX xxxx
019 XXX xxxx ---> 0109 XXX xxxx
0151 XXX xxxx ---> 0101 XXX xxxx

011 XXX xxxx ---> 0111 XXX xxxx
014 XXX xxxx ---> 0114 XXX xxxx
0152 XXX xxxx ---> 0112 XXX xxxx

I am working on a way to modify our address book, so I exported it to Excel.
Now I need a formula (preferred), or a script that would make this change to the whole sheet.
0
Ehab Salem
• 9
• 6
• 3
2 Solutions

Commented:
Hi,ehabsalem.

``````=VLOOKUP(MID(A1,1,FIND(" ",A1,1)-1),{"012","0122";"017","0127";"018","0128";"0150","0120";"010","0100";"016","0106";"019","0109";"0151","0101";"011","0111";"014","0114";"0152","0112"},2,0)
``````

Regards,
Brian
0

Commented:
Apologies, the previous just gives you the new area code, so...
``````=VLOOKUP(MID(A1,1,FIND(" ",A1,1)-1),{"012","0122";"017","0127";"018","0128";"0150","0120";"010","0100";"016","0106";"019","0109";"0151","0101";"011","0111";"014","0114";"0152","0112"},2,0) & " " & MID(A1,FIND(" ",A1,1)+1,9999)
``````

Regards,
Brian.
0

Commented:
... and if there are any codes not changing...
``````=IFERROR(VLOOKUP(MID(A1,1,FIND(" ",A1,1)-1),{"012","0122";"017","0127";"018","0128";"0150","0120";"010","0100";"016","0106";"019","0109";"0151","0101";"011","0111";"014","0114";"0152","0112"},2,0) & " " & MID(A1,FIND(" ",A1,1)+1,9999),A1)
``````

(BTW, is it possible that there will be entries where the old area code is not followed by a space? If so, please let me know as the above will need a change.)

Regards,
Brian.
0

Author Commented:
Thanks Brian, but I am getting #VALUE!
0

Author Commented:
With the last formula, no #VALUE! but values are intact--no change.
0

Commented:
No doubt someone will come up with a fancier formula, but here's mine attached.  It's a basic IF statement like this =IF(LEFT(B2,3)="012","0122"&RIGHT(B2,LEN(B2)-3),) which is then nested 11 times and modified for you 11 rules.
Egypt-mobiles.xlsx
0

Commented:
ehabsalem,

I should have said that the formula starts in B1. Please see attached.

Regards,
Brian.
Area-Codes.xlsm
0

Author Commented:
ok both your formulas are doing the job only when the format is in the form I specified:
010 xxx xxxx, because it is dealing with the cell as text.
The problem is most of the numbers are stored in the format: xxxxxxxxxx, so no spaces, and I had to add ' at the beginning (or actually Excel did it by itself) to force Excel to mark it as text so it won't delete the 0 in the beginning.
How to change the formulas to meet this?
0

Commented:
ehabsalem,

Brian.
Area-Codes-V2.xlsm
0

Commented:
Well we're both using text functions to replace and join text strings.  This can't be done with numbers.  It sounds like some of your numbers are text too.  I think you would be better to treat it all as text to keep the leading zeros anyway.

If you want to force it to numbers at the end, you can simply make a new column and format it as numbers.  Then either (a) copy your results column into notepad and then copy into your newly formatted empty column, or (b) in the new empty column use the formula =C1+1 where C1 is your reuslts column.

I hope that makes sense.  If you can't make this work, try posting us a sample of some of your numbers,
0

Commented:
ehabsalem,

How is the format a problem to you? As you can see, my attached example handles it quite happily.

Brian.
0

Author Commented:
I am attaching part of my sheet, actually theras2000 formula is working (don't know why it didn't work for me first time), but Brian's works only in the last 2 cells.
Book2.xlsx
0

Author Commented:
I tried Brian's from the last sheet he attached and it is working as well.

I do owe you both an appology.

0

Commented:
ehabsalem,

"... Brian's works only in the last 2 cells" - because you're using the old formula (the one created for the format you specified originally). Please see Area-Codes-V2.xlsm above.

Brian.
0

Commented:
ehabsalem,

Apologies, crossing posts.

Brian.
0

Author Commented:
Thanks a lot for your help.
0

Commented:
Thanks, ehabsalem.
0

Commented:
cheers and glad you got it in th end.
0

## Featured Post

• 9
• 6
• 3
Tackle projects and never again get stuck behind a technical roadblock.