Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do you create a new special cell format in Excel?
I have a list of cells that should contain 9 digits. I need to force a zero
as the first character if the cell only contains 8 digits. (Not all of them import as 9 digits). I also need to force a dash after the second digit. I need to have it display as 12-3456789 or as 01-2345678. Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do you create a new special cell format in Excel?
right click the column, format cells, custom
"Lori" wrote: I have a list of cells that should contain 9 digits. I need to force a zero as the first character if the cell only contains 8 digits. (Not all of them import as 9 digits). I also need to force a dash after the second digit. I need to have it display as 12-3456789 or as 01-2345678. Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do you create a new special cell format in Excel?
Excel 2003 - Format, cells, number tab, custom category
and enter 00"-"0000000 Excel 2007 - home tab, cells group, format, format cells, number tab, custom category and enter 00"-"0000000 The dash will always appear in the eigth position from the right and any numbers less than nine digits will display leading zeros. "Lori" wrote: I have a list of cells that should contain 9 digits. I need to force a zero as the first character if the cell only contains 8 digits. (Not all of them import as 9 digits). I also need to force a dash after the second digit. I need to have it display as 12-3456789 or as 01-2345678. Thanks! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do you create a new special cell format in Excel?
Assume your data is in A1:A100
In column B, starting in B1, enter this formula =IF(LEN(A1)<9,REPT(0,9- LEN(A1))&LEFT(A1,1)&"-"&MID(A1,2,7),LEFT(A1,2)&"-"&MID(A1,2,7)) Fill down as needed, then paste values to hard-code the result HTH, JP On Nov 14, 12:37 pm, Lori wrote: I have a list of cells that should contain 9 digits. I need to force a zero as the first character if the cell only contains 8 digits. (Not all of them import as 9 digits). I also need to force a dash after the second digit. I need to have it display as 12-3456789 or as 01-2345678. Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002; cell format confuses Custom and Special | Excel Discussion (Misc queries) | |||
Special Cell Format | Excel Discussion (Misc queries) | |||
Excel needs a special cell format for MAC addresses | Excel Discussion (Misc queries) | |||
Special format for feet - inches in a cell | Excel Discussion (Misc queries) | |||
excel data label format special number characters | Excel Discussion (Misc queries) |