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...
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...
Apologies, the previous just gives you the new area code, so...
Regards,
Brian.
=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.
... and if there are any codes not changing...
(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.
=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.
ASKER
Thanks Brian, but I am getting #VALUE!
ASKER
With the last formula, no #VALUE! but values are intact--no change.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ehabsalem,
I should have said that the formula starts in B1. Please see attached.
Regards,
Brian.
Area-Codes.xlsm
I should have said that the formula starts in B1. Please see attached.
Regards,
Brian.
Area-Codes.xlsm
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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,
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,
ehabsalem,
How is the format a problem to you? As you can see, my attached example handles it quite happily.
Brian.
How is the format a problem to you? As you can see, my attached example handles it quite happily.
Brian.
ASKER
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
Book2.xlsx
ASKER
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...
I do owe you both an appology.
Thank you for your help...
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.
"... 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.
ehabsalem,
Apologies, crossing posts.
Brian.
Apologies, crossing posts.
Brian.
ASKER
Thanks a lot for your help.
Thanks, ehabsalem.
cheers and glad you got it in th end.
Please try...
Open in new window
Regards,
Brian