October 4, 2007 at 10:42 am
I have an excel file i was provided with that contains id's i need to write a query on, only problem is the leading zeros where removed, so some id's that should look like 0000451 look like 451. Problem is their is over 6,000 and all have varying lengths, but i need to make them all 7 digits long. Right now some are 2,3,4,5,6,7 digits long. What would be the easiest way to format them so they are a total of 7 digits, and automatically adding leading zeros as needed?
October 4, 2007 at 10:44 am
SELECT
LEFT('0000000' + CAST(yourNumber AS VARCHAR(7)), 7) AS formattedNumber
FROM
....
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 4, 2007 at 10:46 am
That only returns them. If you need to update the field....
First, what is the datatype of the column? If it's numeric, then I'd suggest leaving it alone and using a view to apply the formatting changes.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 4, 2007 at 11:12 am
This data is in the database correctly, its the excel file that i need to padd with zeros, so i can then use those values in my query. So the solution is not using sql, it would be something else. I know i can use access and add the zeros, but i need an easier way to automatically add the zeros to make 7 digits.
October 4, 2007 at 11:18 am
=TEXT(A1, "0000000")
will convert a value of 452 in cell A1 to 0000452.
Is that what you need?
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 4, 2007 at 11:19 am
I have an add on program in execl that will let me padd with zeros, so i did a blanket padd of 5 zeros, but how cut i cut off the extra so only 7 digits are displayed? i.e. some numbers are showing as 00000001245 and i need to turn that into 0001245. The numbers that are showing correctly i.e. 0001459 should not be affected, because they are already 7 digits, anyway to do this in excel?
October 4, 2007 at 11:25 am
Jason Selburg (10/4/2007)
=TEXT(A1, "0000000")will convert a value of 452 in cell A1 to 0000452.
Is that what you need?
Thats similar to what i did, but now since some numbers were only 3 digits, they became 0000123 which is fine, but some numbers started out as being 5 digits, so they became 000014526 which is fine, but i need to cut the leading zeros to make it only 7 digits i.e. 0014526(as thats how its stored in the database) Does this make sense?
October 4, 2007 at 11:27 am
=LEFT(TEXT(A1, "0000000"), 7)
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 4, 2007 at 11:33 am
Im sorry im not too knowledgeable with execl, i tired highlighting the column, and adding your code in the fx box, but it didnt work. Im sure im missing something silly here, thanks for your help btw!!
October 4, 2007 at 11:39 am
Enter the formula in ONE cell at the top of the list, click enter. Select the cell with the formula in it. Click and HOLD the bottom right corner of the cell it will have a black square in the corner. now drag that cell down to the bottom of your worksheet.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 4, 2007 at 11:43 am
hrmm, when i do that it turns the number to zero, myabe this would be easier in access?
October 4, 2007 at 11:44 am
What are you trying to accomplish? Not the adding leading zeros part, but what are you doing with the data after adding the zeros?
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 4, 2007 at 11:50 am
Sorry i wasnt clear. I was provided a list in excel with previous id's. i.e.
1234 In the database this is displayed as 0001234
0000417 correct
0014789 correct
12 In the database this is displayed as 0000012
1475 ect .....0001475
First i padded everything with leading zeros so at least they were 7 digits or more, the more being the leading zeros. Now, i need to just pull from the far right the first 7 digits, so if i had 00000004178 i would somehow neeed to make that 0004178, if its 0000478 already, then i dont need to make a change, again pulling only the seven digits from the right. In the end, i need to use these numbers to pull a query so i can display their updated id's because we store both the previous and updated id's in the database, but the previous id's are strictly for archival purposes.
October 4, 2007 at 11:54 am
OOPS, My previous formula was wrong. "=RIGHT(TEXT(A1, "0000000"), 7)"
OK, so are you using excel to help in creating SELECT or UPDATE statements.
If so, then the above will work and it won't give you just zeros.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 4, 2007 at 11:59 am
When i use that it just displayes the code in the cell. My first few colums look like this
000012323 a1
00001240 a2
000012405 a3
00001244 a4
0000000012475 a5
Am i missing something here?
For example a1, has 2 extra zeros i dont need should look like this 0012323
and a5 has 6 extra zeros, it should look like 0012475. That make sense?
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply