copying format values -phone

[gtjr92]

I have several spreadsheets that have thousand of phone numbers
i need the phone number values to be like (123) 233-5121
If I have a number like 1232335121 I can change the format to look like a phone number but that
does not change the actual value.
Is there a way to either
1. copy the format and past it as a value.
I tried this with copy and past values but of course that did no good.
2. if 1. is not possible is there some sort of macro or something that I can create that will go in and put parentheses around the 1st 3 numbers add a space and then a - in between 6th and 7th number.

Thanks

[Leith Ross]

Hello gtjr92,

Welcome to the forum! As you pointed out, standard copying methods won't work. You can copy a formatted number as a text value to another cell using a macro. This macro can be used like a worksheet function. It will copy the formatted value as text into the cell that the macro is in.

Please Login or Register  to view this content.

Adding the Macro
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time
8. Save the Macro by pressing the keys CTRL+S

Worksheet Example
Cell A1 contains the value 1232335121 and is formatted as Phone Number
Cell A1 displays the value 1232335121 as (123) 233-5121
Cell A2 contains the formula =AsText(A1)
Cell A2's value is now the text string "(123) 233 -5121" without the quotes

Sincderely,
Leith Ross

[gtjr92]

that worked perfect, How can I save this to excel so it is globally available to
any worksheet i open.
Is there any way to export it so i can use it on another pc?
Thanks again!

[Leith Ross]

Hello gtjr92,

If you followed all my directions when installing the macro, it is already saved in your workbook (See step 9). You can use the Export feature of the Visual Basic Editor to save the new module that contains the macro to a folder of your choosing. You can later use the Import function to add this macro to any Excel workbook.

You can rename the module from its default name of Module1 to something more descriptive, like the macro's name. The name can be a maximum of 31 characters. No spaces are allowed in the name, but you can use underscores and hyphens to separate words. In the VBE press F4 to display the Properties Window. Modules have only one property - Name. Type the new name in the box to the right of Name.

To get to the VBE (Visual Basic Editor) from Excel press the keys ALT+F11. Look under File on the menu and you will See both Export and Import.

Sincerely,
Leith Ross

[gtjr92]

I followed your directions to a t.
If i am understanding you correctly I have to import this to every work book.
I can't have it automatically available with a new workbook i create or anything??

[Leith Ross]

Hello gtjr92,

You could also add the macro to your Personal.xls workbook. Then it will be available to all workbooks on your computer without having to import the macro file. The Personal.xls is a hidden workbook that opens when Excel starts. See this link for more information...

Deploying Your Excel Macros

Sincerely,
Leith Ross

[gtjr92]

thanks a bunch for your excellent assistance!!

[gtjr92]

I got the personal xls file and i created a macro and i was able to access that with any workbook.
However the function asText will only work within the personal.xls file and not globally.
Any ideas?
Thanks so much