Ehab Salem

asked on

# 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

(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.

__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.

ASKER

Thanks Brian, but I am getting #VALUE!

ASKER

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

SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**
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

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**
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