Format a Phone Number

**scotfitz** · 12-23-2009, 05:30 PM

I am using Excel to clean up a name and address list before
I export it to Access.

I now have parsed a field to yield the result 812 3340132
and for the final step I would like to remove the space between
812 and 3320132 and add a 1 to the front of the new number
which would then read 18123320132.

Can I do this as a Find and Replace action in the existing column
or do I need to create a new column with an entirely new formula?

Thanks in advance.

**shg** · 12-23-2009, 05:36 PM

You could do this from the user interface. Select the columns, replace space with nothing. Then put =10000000000 in a cell, copy, select the cells of interest, paste special, add.

Or use a formula: =10000000000 + SUBSTITUTE(A1, " ", "")

**teylyn** · 12-23-2009, 06:03 PM

after removing the space you could also format the number to just show the leading 1 without actually storing it in the number. Use custom format

1#

Or for a formula approach

=text(a1,"1#")

**shg** · 12-23-2009, 06:58 PM

format the number to just show the leading 1

Don't think the 1 will export to Access if you do that ...

Or for a formula approach
=text(a1,"1#")

Might export as Text to Access if you do that ...

**teylyn** · 12-23-2009, 07:01 PM

Don't think it will export to Access if you do that .

completely missed the Access aspect. Thanks for pointing that out, shg.

Format a Phone Number

LinkBack

Thread Tools

Rate This Thread

Display

Format a Phone Number

Re: Formatting Phone Number

Re: Formatting Phone Number

Re: Format a Phone Number

Re: Format a Phone Number

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions