Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
trying to change 000-000-0000 phone format to (000)000-0000 ????
This is from a data base that was given to me. I have tried to format the
cells, but it won't change them. Any suggestions? thanks, Jean |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
trying to change 000-000-0000 phone format to (000)000-0000 ????
I know there can be better answer than this one. but this one works.
suppose the number is in A1. In B1, write ="("&LEFT(A1,3)&")"&MID(A1,5,3)&RIGHT(A1,5) drag it down all the way down. once done, COPY the entire column and PASTE SPECIAL VALUES. "Labtyda" wrote in message ... This is from a data base that was given to me. I have tried to format the cells, but it won't change them. Any suggestions? thanks, Jean |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
trying to change 000-000-0000 phone format to (000)000-0000 ????
="(" & LEFT(A1,3) & ")" & RIGHT(A1,8)
-- Gary''s Student - gsnu200772 "Labtyda" wrote: This is from a data base that was given to me. I have tried to format the cells, but it won't change them. Any suggestions? thanks, Jean |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
trying to change 000-000-0000 phone format to (000)000-0000 ????
Assuming you have the current phone number in cell A2, for instance, the
following could be put into cell B2: ="("&Left(A2,3)&")"&Right(A2,8) "Labtyda" wrote: This is from a data base that was given to me. I have tried to format the cells, but it won't change them. Any suggestions? thanks, Jean |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
trying to change 000-000-0000 phone format to (000)000-0000 ????
You could use Excels special format for phone numbers which is
(000)-000-0000 which will format 1234567890 as (123)-456-7890 or you could make your own custom format of (000)000-0000 which will format 1234567890 as (123)456-7890 Tyro "Labtyda" wrote in message ... This is from a data base that was given to me. I have tried to format the cells, but it won't change them. Any suggestions? thanks, Jean |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
trying to change 000-000-0000 phone format to (000)000-0000 ????
Assuming you have a lot of these and want to convert them in place, then you
can use this macro to do that... Sub ReformatPhonenumbers() Dim R As Range For Each R In UsedRange If R.Value Like "###-###-####" Then R.Value = "(" & Replace(R.Value, "-", ")", , 1) End If Next End Sub Anything having 3 digits followed by a dash followed by 3 more digits followed by another dash followed by 4 digits will be converted. By the way, if you meant there to be space after the closing parenthesis, then put a space after the ")" inside of the Replace function. Rick "Labtyda" wrote in message ... This is from a data base that was given to me. I have tried to format the cells, but it won't change them. Any suggestions? thanks, Jean |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to put dashes in a number 13000000000 to 1300-000-0000 | Excel Discussion (Misc queries) | |||
How can I get 0000 in Every Column N every Excel file by default? | Excel Discussion (Misc queries) | |||
what is the minimum numbers set for 4 digit numbers from 0000 to 9 | Excel Discussion (Misc queries) | |||
Phone number format from 000.000.0000 to (000)000-0000 | Excel Discussion (Misc queries) | |||
(social security number) 000-00-0000 to 000000000 | Excel Discussion (Misc queries) |