Using Fill - with a twist!

**PamelaDV** · 07-14-2008, 10:18 AM

Hello All. I'm hoping someone reading this will be able to help me out. I am familiar with using the Fill function in Excel, but I have a particular challenge that I can't seem to get to work.

I have a list of SSN #'s. I need to increment them by 1 - but in the middle. For example:

001-01-0001
002-01-0001
003-01-0001

Get it? That third number is the one I need to increment by 1 and fill down the column. Does anyone have any ideas on how I can get this done quickly?

Thanks!

PamelaDV

**NBVC** · 07-14-2008, 10:23 AM

tRY, in A2:

=TEXT(LEFT(A1,3)+1,"000")&MID(A1,4,255) copied down

Where A1 contains firs s/n in series..

**Ron Coderre** · 07-14-2008, 10:28 AM

If you only need to display the values, but don't need the actual entire SSN,
try this:

A1: 1

Then....From the Excel Main Menu:
<format><cells>
Category: Custom
Type: "001-"00"-0001"
Click [OK]

(A1 will display 001-01-0001 )

Now you can use the fill functionality to continue the list down Col_A.
A1: 001-01-0001
A2: 001-02-0001
A3: 001-03-0001
etc

Is that something you can work with?
Post back if you have more questions.

**PamelaDV** · 07-14-2008, 11:03 AM

That does work well - but it is increasing the wrong number. How would I enter that custom format if I want to increase the third number instead of the 5th? Like this:

001-00-0000
002-00-0000
003-00-0000

Thanks for your help!

**Ron Coderre** · 07-14-2008, 11:08 AM

Apologies....I misread what you posted.

Easily fixed....just change the custom number format to this:

000"-00-000"

All set?

**PamelaDV** · 07-14-2008, 12:45 PM

Ron - thank you so much for your assistance.

I am still getting trouble. When I put that format into the Custom formatting - it increases the last number by 1 instead of the third number. It does this:

001-00-0000
001-00-0001
001-00-0002

instead of:
001-00-0000
002-00-0000
003-00-0000

I tried playing around with the custom formating to no avail. ;-( Any other ideas?

PamelaDV

**NBVC** · 07-14-2008, 01:09 PM

You can always try my suggestion...

**PamelaDV** · 07-14-2008, 01:15 PM

I did use your suggestion and it worked great! I am really looking for a way to have the actual value in the field, though. Is there a way to convert the cell to store the value once I have done the calculation the way you wrote it?

**NBVC** · 07-14-2008, 01:25 PM

Copy the whole column, then select the top cell in the column and go to Edit|Paste Special and select Values...

click Ok.

**Ron Coderre** · 07-14-2008, 01:52 PM

Actually, this is probably easiest.....

A1: 1000000
<format><cells>
...Category: Special
...Type: Social Security Number
Click [OK]

A2: =A1+1000000
Copy A2 and paste down as far as you need.

Does that work for you?