NewJeffCT
(1000+ posts)
Send PM |
Profile |
Ignore
|
Wed Dec-06-06 10:44 AM
Original message |
Any excel gurus out there? Converting mixed length numeric to fixed length |
|
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
(1000+ posts)
Send PM |
Profile |
Ignore
|
Wed Dec-06-06 11:02 AM
Response to Original message |
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
(1000+ posts)
Send PM |
Profile |
Ignore
|
Wed Dec-06-06 11:05 AM
Response to Reply #1 |
|
but, from what she says, it is very complicated using the program she is using.
|
LynzM
(1000+ posts)
Send PM |
Profile |
Ignore
|
Wed Dec-06-06 11:34 AM
Response to Original message |
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
(1000+ posts)
Send PM |
Profile |
Ignore
|
Wed Dec-06-06 11:47 AM
Response to Reply #3 |
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
(1000+ posts)
Send PM |
Profile |
Ignore
|
Wed Dec-06-06 01:04 PM
Response to Reply #4 |
5. LOL, no worries, dude.... |
|
It just so happens that I work with data and databases, so... yeah. :) :hi:
|
Yavin4
(1000+ posts)
Send PM |
Profile |
Ignore
|
Wed Dec-06-06 01:42 PM
Response to Reply #4 |
6. I Strongly Suggest Making a Backup Copy of Your Excel Data |
|
before you re-format the cells.
|
NewJeffCT
(1000+ posts)
Send PM |
Profile |
Ignore
|
Wed Dec-06-06 01:49 PM
Response to Reply #6 |
|
Though, I still have the original Access DB, if necessary.
|
DU
AdBot (1000+ posts) |
Fri May 10th 2024, 01:14 PM
Response to Original message |