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

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.
Your help is appreciated...
0
Ehab Salem
Asked:
Ehab Salem
  • 9
  • 6
  • 3
2 Solutions
 
redmondbCommented:
Hi,ehabsalem.

Please try...
=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)

Open in new window


Regards,
Brian
0
 
redmondbCommented:
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)

Open in new window


Regards,
Brian.
0
 
redmondbCommented:
... 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)

Open in new window


(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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
Ehab SalemAuthor Commented:
Thanks Brian, but I am getting #VALUE!
0
 
Ehab SalemAuthor Commented:
With the last formula, no #VALUE! but values are intact--no change.
0
 
theras2000Commented:
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
 
redmondbCommented:
ehabsalem,

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

Regards,
Brian.
Area-Codes.xlsm
0
 
Ehab SalemAuthor 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
 
redmondbCommented:
ehabsalem,

Please see attached.

Brian.
Area-Codes-V2.xlsm
0
 
theras2000Commented:
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
 
redmondbCommented:
ehabsalem,

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

Brian.
0
 
Ehab SalemAuthor 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
 
Ehab SalemAuthor Commented:
I tried Brian's from the last sheet he attached and it is working as well.

I do owe you both an appology.

Thank you for your help...
0
 
redmondbCommented:
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
 
redmondbCommented:
ehabsalem,

Apologies, crossing posts.

Brian.
0
 
Ehab SalemAuthor Commented:
Thanks a lot for your help.
0
 
redmondbCommented:
Thanks, ehabsalem.
0
 
theras2000Commented:
cheers and glad you got it in th end.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

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