Need help padding numbers

[narpet]

Hello all.

I have a problem in an Excel spreadsheet. I have a column with numbers in it where I need to change the value to conform to a certain format.

For example I may have a set of numbers that look like this in the column:

12345
123456
1234567
12345678

I need them all to be changed to conform to this format:

XXXXXXXX-000

with leading zeros to fill in the gaps. So the above numbers would be converted to:

00012345-000
00123456-000
01234567-000
12345678-000

I would like to create the code in excel as a macro and attach it to a button to either fix all numbers in the column or fix one at a time based on active cell.

Any help will be greatly appreciated.

Thanks,
narpet

[narpet]

Forgot to add that I may have a number that is already formatted correctly, like:

00123456-000

So I would have to add to the code to not change that number....

Hello all.

I have a problem in an Excel spreadsheet. I have a column with numbers in it where I need to change the value to conform to a certain format.

For example I may have a set of numbers that look like this in the column:

12345
123456
1234567
12345678

I need them all to be changed to conform to this format:

XXXXXXXX-000

with leading zeros to fill in the gaps. So the above numbers would be converted to:

00012345-000
00123456-000
01234567-000
12345678-000

I would like to create the code in excel as a macro and attach it to a button to either fix all numbers in the column or fix one at a time based on active cell.

Any help will be greatly appreciated.

Thanks,
narpet

[narpet]

Never mind... I figured it out.

Thanks,
narpet

[Killer42]

Never mind... I figured it out.

I hope you're doing it by changing just the display format, and not the value?

[narpet]

Nope... it was already configured to display the way I wanted. The problem was that this information is sent to the state, and they will only take it if the actual data is formatted as I described, and it must be sent to them in a text file.

So I wrote a macro that changes the data to look the way the state wants it. Works perfect.

Thanks,
narpet