Add characters to numbers in cells

[Ozhatch]

I have a list with three columns of telephone numbers - some 200 numbers in all. To facilitate dialing an international number directly from the cells, I need to add '+64 to the beginning of each number. e.g. 98294578 becomes '+6498294578. (The ' sign is so that the cell doesn't see the + as a part of a formula.) I tried the find/replace feature, but that returns some really peculiar results, such as '+64982'+6494578! Does anybody have any suggestions?

 

[Akashwani]

Hi,

The only solution I can offer is a custom number format like this....

"+64"0

Example...

Excel Workbook
	A	B
1	Number	*
2	+6498294578	*
3	+6498294588	*
4	+6498294599	*
5	*	*
Sheet11

Highlight the range that contains the numbers.
Right click.
Click Format Cells.
Click Number, click Custom and paste the above custom number format into the white box below "Type".
Click OK.

I hope that helps.

Ak

 

[L. Howard]

Try this:

="+64" & F2
Where F2 is the 98294578.

Copy and past special values to get rid of the formula.

Regards,
Howard

 

[Ozhatch]

Fantastic! It worked. Thanks so much AK!

 

[Ozhatch]

AK, I did what you suggested, and while the result appears to do what I want, when I click in the cell the number reverts to its original form. I need to copy the whole number, including the international dial code, but the +64 isn't picked up.

 

[Ozhatch]

Thanks, LH. I'm not sure I understand your solution. Please would you elucidate? Thanks.

 

[Rick Rothstein]

I have a list with three columns of telephone numbers - some 200 numbers in all. To facilitate dialing an international number directly from the cells, I need to add '+64 to the beginning of each number. e.g. 98294578 becomes '+6498294578. (The ' sign is so that the cell doesn't see the + as a part of a formula.) I tried the find/replace feature, but that returns some really peculiar results, such as '+64982'+6494578! Does anybody have any suggestions?

First select the three columns containing your phone numbers, then give this macro a try...

Sub PrefixPlus64()
  Intersect(Selection, ActiveSheet.UsedRange).Select
  Selection = Evaluate(Replace("IF(LEN(@),IF(ISNUMBER(-@),""'+64""&@,@),"""")", "@", Selection.Address))
End Sub

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (PrefixPlus64) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. Since I am guessing you only need to do once, you can delete the macro code after you have run it... after that, do not forget to save the workbook (assuming the code actually did what you wanted it to, of course).

 

[Ozhatch]

Thank you very much, Rick. That's an elegant solution. I have so much to learn in this program. I'm very grateful that this forum exists, and I really appreciate your assistance.