Any excel gurus out there? Converting mixed length numeric to fixed length

[NewJeffCT]

I have several excel spreadsheets where I am trying to compare my data to test data. I have been dealing with a very nice programmer who says she can whip up a quick program to help me match the files (some of which can be thousands of lines long, as I'm drawing from an almost 5 million line Access database)

However, one thing that is causing her problems is that on her end, the numeric value column of the data is a fixed length – (not sure how it works, but maybe 555.55 would be 000000555.55) while in my excel spreadsheets, the numbers are just regular numbers – meaning 555.55 would be six characters long – including the decimal, while 123456.78 would be nine characters long.

I’m almost positive there is a way to do it, but I’m drawing a blank right now. Is there a way to convert my column of variable length numbers to a set fix length, i.e. 12 characters or xxxxxxxxx.xx or similar?

Thanks

[Yavin4]

1. Your Programmer Should Write That Code

When she reads in the numbers from your Excel, she should write some code to convert the numbers int the right format. You should not mess with your Excel data because you may lose some of it. Practically all programming languages have built-in code to convert numbers into different formats.

[NewJeffCT]

2. she can do that

but, from what she says, it is very complicated using the program she is using.

[LynzM]

3. Within excel, you can define a fixed format

Select the data you want to reformat. Go to format --> cells, and choose custom (bottom of the list). Pick the one that looks closest to what you want (probably 0.00) and then edit it to (0000000.00) or whatever. This will add the zeros to the front.

[NewJeffCT]

4. thanks - I knew it was easy

If this was 10 years ago, i probably could do that in my sleep. But, alas, I'm old now.

[LynzM]

5. LOL, no worries, dude....

It just so happens that I work with data and databases, so... yeah. :) :hi:

[Yavin4]

6. I Strongly Suggest Making a Backup Copy of Your Excel Data

before you re-format the cells.

[NewJeffCT]

7. too late for that

Though, I still have the original Access DB, if necessary.