Changing Number format

R

Randy

I have a document that I've saved into Excel.. the format for the phone
number field is ###-###-####.. I want to take out the dashes.. the format for
the phone number column is in Text format but if I need to convert it to
numbers to fix this it's an easy fix.. but I want the numbers to appear as
##########.. Can somebody help me with this

Thanks
 
V

Val

how about:

=CONCATENATE(LEFT(A1,3),MID(A1,5,3),RIGHT(A1,4))

Val

I have a document that I've saved into Excel.. the format for the phone
number field is ###-###-####.. I want to take out the dashes.. the format
for
the phone number column is in Text format but if I need to convert it to
numbers to fix this it's an easy fix.. but I want the numbers to appear as
##########.. Can somebody help me with this

Thanks
 
R

Randy

This worked in Excel.. however I'm trying to import this 'field' into
Access.. and as the formula, Access has a problem with it.. Is there some way
I can copy the resulting string of numbers.. into another row of cells as a
text string?.. and not as a formula?
 
V

Val

My brute force approach would be to do the converion in Excel. Copy the
column with the converted data, and use "paste special" to paste the
values - you can put them right in place of the formula. You then have a
text version of the data.


That "paste special" option in the Edit menu or the context menus is a
really helpful tool!

Val


This worked in Excel.. however I'm trying to import this 'field' into
Access.. and as the formula, Access has a problem with it.. Is there some
way
I can copy the resulting string of numbers.. into another row of cells as a
text string?.. and not as a formula?
 
R

Randy

Thank you very much.. Mission accomplished.. sometimes 'brute force' is the
best approach!!
 
B

Beth Melton

I see that you have a resolution to this but for future reference, if you
need to remove a character that's stored with your data you can use
Find/Replace (Ctrl+F). Just select the column to confine your replacement
selection and in the Find text box type the dash and leave the Replace text
box blank. When you click Replace All the dashes will be deleted.

Please post all follow-up questions to the newsgroup. Requests for
assistance by email cannot be acknowledged.

~~~~~~~~~~~~~~~
Beth Melton
Microsoft Office MVP

Coauthor of Word 2007 Inside Out:
http://www.microsoft.com/MSPress/books/9801.aspx#AboutTheBook

Word FAQ: http://mvps.org/word
TechTrax eZine: http://mousetrax.com/techtrax/
MVP FAQ site: http://mvps.org/
 
Top