| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
updating phone number formatting
Hi everyone, I need help, again. I have several worksheet that I need to combine the information in order to upload into a access database. I problem that I'm having is the phone numbers are formatted differently therefore they are not all of the are uploading. The Access data base field is classified as text. The phone numbers that did upload correct are formatted as special / phone numbers. I tried changing the cells formatting but it doesn't update the existing information. It does update if I type the number in by hand. Is there away of updating the existing numbers to be formatted like the example below. (555) 777-4444. All suggestions are always appreciated. Thanks Lostinformulas -- lostinformulas ------------------------------------------------------------------------ lostinformulas's Profile: http://www.excelforum.com/member.php...o&userid=35229 View this thread: http://www.excelforum.com/showthread...hreadid=569094 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
updating phone number formatting
Hi, The format you want is (###) ###-####. However if your numbers has spaces it will read as text. You could use a extra column with this formula to remove one space =SUBSTITUTE(A1," ","",1) Drag down and covert to number then custom format as above. If this doen't help a sample of the data would be useful VBA Noob. -- VBA Noob ------------------------------------------------------------------------ VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833 View this thread: http://www.excelforum.com/showthread...hreadid=569094 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
updating phone number formatting
This is how the numbers are formatted in column "G" 800-942-5590 484-553-2066 254-715-2503 952-294-2990 715-284-5732 734-326-7844 405-387-9415 205-792-1208 Thanks lostinformulas -- lostinformulas ------------------------------------------------------------------------ lostinformulas's Profile: http://www.excelforum.com/member.php...o&userid=35229 View this thread: http://www.excelforum.com/showthread...hreadid=569094 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
updating phone number formatting
So say in Col H1 enter the below and drag down =SUBSTITUTE(G1,"-","") Then copy and paste special values. Next you should get excel paste box options. Select and change to number. Next custom format cells as (###) ###-#### Hopefully job done. VBA Noob -- VBA Noob ------------------------------------------------------------------------ VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833 View this thread: http://www.excelforum.com/showthread...hreadid=569094 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
updating phone number formatting
Thanks that worked great! Lostinformulas -- lostinformulas ------------------------------------------------------------------------ lostinformulas's Profile: http://www.excelforum.com/member.php...o&userid=35229 View this thread: http://www.excelforum.com/showthread...hreadid=569094 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
updating phone number formatting
lost,
i wish that excel had masking similar to Access, so that numbers that will never get calculated, like phone, zip or part numbers could be entered (as text) but be displayed with parentheses, dashes, points in the correct places. Does anyone know if MS has plans or this in future release? Beege "lostinformulas" <lostinformulas.2c6kc6_1154971815.9888@excelforu m-nospam.com wrote in message news:lostinformulas.2c6kc6_1154971815.9888@excelfo rum-nospam.com... Hi everyone, I need help, again. I have several worksheet that I need to combine the information in order to upload into a access database. I problem that I'm having is the phone numbers are formatted differently therefore they are not all of the are uploading. The Access data base field is classified as text. The phone numbers that did upload correct are formatted as special / phone numbers. I tried changing the cells formatting but it doesn't update the existing information. It does update if I type the number in by hand. Is there away of updating the existing numbers to be formatted like the example below. (555) 777-4444. All suggestions are always appreciated. Thanks Lostinformulas -- lostinformulas ------------------------------------------------------------------------ lostinformulas's Profile: http://www.excelforum.com/member.php...o&userid=35229 View this thread: http://www.excelforum.com/showthread...hreadid=569094 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Replacing data | Excel Discussion (Misc queries) | |||
| Phone Number formatting | Excel Worksheet Functions | |||
| Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
| Formatting Phone Numbers in a Merged Cells... | Excel Discussion (Misc queries) | |||
| concatenating and formatting area code and phone number columns | Excel Worksheet Functions | |||
Linear Mode
