I need to make some labels in the following format:

001-1 to 216-1

I tried ###"-1" but this does not increment the 1st 3 digits but does
increment the -1. I also need to display the leading zero's

These need to be in one cell each

TIA

You have at least a couple options:

Examples:
B3: =TEXT(ROW()-2,"000-1")
Copy down as far as you need

OR....
A1:A216 contains the series 1 through 216
Select that range...
Then...From the Excel main menu:
<format<cells<number tab
Categor: Custom
Type: 000-1
Click [OK]

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP


"jollynicechap" wrote:

I need to make some labels in the following format:

001-1 to 216-1

I tried ###"-1" but this does not increment the 1st 3 digits but does
increment the -1. I also need to display the leading zero's

These need to be in one cell each

TIA

Not sure how you want the numbers to increment, but try custom format of
000-0. If you then start by entering 11, it will display as 001-1. 12 will
display as 001-2. 20 will display as 002-0, 21 = 002-1
--
Hth

Kassie Kasselman


"jollynicechap" wrote:

I need to make some labels in the following format:

001-1 to 216-1

I tried ###"-1" but this does not increment the 1st 3 digits but does
increment the -1. I also need to display the leading zero's

These need to be in one cell each

TIA

Thanks Ron

Both options work a treat. But I changed =TEXT(ROW()-2,"000-1") to
=TEXT(ROW()-3,"000-1") to start from 001. Why? I don't know. I thought it
would be =TEXT(ROW()-1,"000-1"), but this starts at 003??

Paul

Try

=TEXT(ROW(A1)-3,"000-1")

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"jollynicechap" wrote in message
...
Thanks Ron

Both options work a treat. But I changed =TEXT(ROW()-2,"000-1") to
=TEXT(ROW()-3,"000-1") to start from 001. Why? I don't know. I thought it
would be =TEXT(ROW()-1,"000-1"), but this starts at 003??

Paul

=TEXT(ROW()-3,"000-1") works for a column but not for a Row. Each row
contains six numbers, one number per cell

Example of label:

001-1 001-1 001-1 001-1 001-1 001-1
Passive Floor Distributor 1
007-1 008-1 009-1 010-1 011-1 012-1

Use the ROW function to copy down a column
=TEXT(ROW()-3,"000-1")

Use the COLUMN function to copy across a row
=TEXT(COLUMN()-3,"000-1")

Notes: The ROW or COLUMN functions, when used with no parameters, return the
row number or column number of the cell containing the function. You can
increment/decrement those values to achieve the number you want.

Example:
B1: =TEXT(COLUMN()+5,"000-1")
returns 006-1

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"jollynicechap" wrote:

=TEXT(ROW()-3,"000-1") works for a column but not for a Row. Each row
contains six numbers, one number per cell

Example of label:

001-1 001-1 001-1 001-1 001-1 001-1
Passive Floor Distributor 1
007-1 008-1 009-1 010-1 011-1 012-1

Thanks Ron for the explanation. I can work it now.