The new forums will be named Coin Return (based on the most recent vote)! You can check on the status and timeline of the transition to the new forums here.
Please vote in the Forum Structure Poll. Polling will close at 2PM EST on January 21, 2025.
How you can use [EXCEL] to get womens* (*not guaranteed)
Ok new problem. I'm trying to build a pivot table based on an access table. It is located in a DB that isn't used by anyone, so I have exclusive rights to it. This is what happens when I go to Excel and attempt to pull data into the spreadsheet:
\
Both the spreadsheet and the Access DB are local, why the F*** can't it pull any data? It reads the records, just fails to display anything in the sheet (shows 3,034 records read in the bottom right corner)
Ok, turns out there is an issue with that database - the same database has been used in Access since 2003 and finally got corrupted. We're circumventing this issue with a new database.
However: new problem.
I need to write a formula that inserts a leading 0 based on how many digits are in a cell.
If a cell has the number 5, it should have 3 zeroes.
If a cell has the number 55, it should have 2 zeroes.
If a cell has the number 555, it should have 1 zero.
This is the suffix of a phone number. When I pull data from the source and import to Excel, it throws away the leading zero - the output is formatted as a number, so there's no way to store it as text first.
I tried this: =if(H30<10,"000",(if(H30>10<100,"00",(if(H30>99,"0",""))))) but it won't work because the file is Excel 2003, so I assume any solution I do should work in Excel 2003.
The problem is I have over 3000 cells that need this done. I need to be able to insert a column, type in this formula (or paste it) throughout the sheet. Since it varies between needing 1 and 3 leading zeroes, even if I sort it, I'd be doing this for 20 minutes. I do this every day, so it needs to be efficient and fast.
Why wouldn't it put zeroes where you need em? They're leading zeroes right?
A 0000 format means 21 will be 0021, 317 will be 0317, etc.
I just tried it, success. Here's the kicker - I need to combine this suffix cell with 2 others (area code and prefix). When you do =A1&B1&C1, the result shows the original number without leading zeroes.
Posts
SUMIFS looks like it would work, just looked it up. And yes, it's 2007 only though.
Both the spreadsheet and the Access DB are local, why the F*** can't it pull any data? It reads the records, just fails to display anything in the sheet (shows 3,034 records read in the bottom right corner)
However: new problem.
I need to write a formula that inserts a leading 0 based on how many digits are in a cell.
If a cell has the number 5, it should have 3 zeroes.
If a cell has the number 55, it should have 2 zeroes.
If a cell has the number 555, it should have 1 zero.
This is the suffix of a phone number. When I pull data from the source and import to Excel, it throws away the leading zero - the output is formatted as a number, so there's no way to store it as text first.
I tried this: =if(H30<10,"000",(if(H30>10<100,"00",(if(H30>99,"0",""))))) but it won't work because the file is Excel 2003, so I assume any solution I do should work in Excel 2003.
Any thoughts?
The problem is I have over 3000 cells that need this done. I need to be able to insert a column, type in this formula (or paste it) throughout the sheet. Since it varies between needing 1 and 3 leading zeroes, even if I sort it, I'd be doing this for 20 minutes. I do this every day, so it needs to be efficient and fast.
A 0000 format means 21 will be 0021, 317 will be 0317, etc.
I just tried it, success. Here's the kicker - I need to combine this suffix cell with 2 others (area code and prefix). When you do =A1&B1&C1, the result shows the original number without leading zeroes.
So try something like =A1&B1&TEXT(C1, "0000")
GENIUS! This does the trick! Thanks man, I didn't know that was possible.
Limed for truth.