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!

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!

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!

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!