how do you create a new special cell format in Excel?

G

Guest

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!
 
G

Guest

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.
 
J

JP

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Adding recognised Zeros 5
default format for text in cells 3
Format>Cells>Special>Zip Code 5
Count number of digits in a cell 6
How do I calculate age using a 12 digit social security number in MS Excel? 0
How to insert a value in one cell into many cells 17
How do I find a dash in a cell with numbers in it in excel. 9
Converting text to numbers 2

Top