Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Strip leading zeros
Using Excel 2003
We manufacture products that have 4 properties, stored in columns B, C, D and E. I create a part number for each product by concatenating the data in the four columns. The property in Column D can be from 1 to 6 characters in length, so when I create the part number, I apply the following formula to the data in column D: RIGHT("00000"&D1,6) to ensure that all part numbers will be of the same length. When the part number is created, it is 13 characters long. What formula can I apply to the part number to extract the original property in column D, stripping away only the leading zeros? TIA David |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Strip leading zeros
If the part number are only numbers you can just force a calculation like
=0+A2 then use general formatting -- Regards, Peo Sjoblom "Compass Rose" wrote in message ... Using Excel 2003 We manufacture products that have 4 properties, stored in columns B, C, D and E. I create a part number for each product by concatenating the data in the four columns. The property in Column D can be from 1 to 6 characters in length, so when I create the part number, I apply the following formula to the data in column D: RIGHT("00000"&D1,6) to ensure that all part numbers will be of the same length. When the part number is created, it is 13 characters long. What formula can I apply to the part number to extract the original property in column D, stripping away only the leading zeros? TIA David |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Strip leading zeros
I'm not sure I understand the layout of your data, but this will remove the
leading 0's in A1: =MID(A1,SEARCH(LEFT(SUBSTITUTE(A1,"0",""),1),A1),2 55) Compass Rose wrote: Using Excel 2003 We manufacture products that have 4 properties, stored in columns B, C, D and E. I create a part number for each product by concatenating the data in the four columns. The property in Column D can be from 1 to 6 characters in length, so when I create the part number, I apply the following formula to the data in column D: RIGHT("00000"&D1,6) to ensure that all part numbers will be of the same length. When the part number is created, it is 13 characters long. What formula can I apply to the part number to extract the original property in column D, stripping away only the leading zeros? TIA David -- Dave Peterson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Strip leading zeros
Thanks, Dave. This is exactly what I'm looking for. I'm also trying to
decifer the formula to learn from your example. "Dave Peterson" wrote: I'm not sure I understand the layout of your data, but this will remove the leading 0's in A1: =MID(A1,SEARCH(LEFT(SUBSTITUTE(A1,"0",""),1),A1),2 55) Compass Rose wrote: Using Excel 2003 We manufacture products that have 4 properties, stored in columns B, C, D and E. I create a part number for each product by concatenating the data in the four columns. The property in Column D can be from 1 to 6 characters in length, so when I create the part number, I apply the following formula to the data in column D: RIGHT("00000"&D1,6) to ensure that all part numbers will be of the same length. When the part number is created, it is 13 characters long. What formula can I apply to the part number to extract the original property in column D, stripping away only the leading zeros? TIA David -- Dave Peterson |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Strip leading zeros
=substitute(a1,"0","")
removes all the zeros from in A1: 000001231A001234 becomes 1231A1234 Then =left("123aA1234",1) picks out the first character in that string ("1"). =mid() looks for the position of that character. 255 is a big number that means bring back that many characters. You could use 12 (I think) if you wanted. Compass Rose wrote: Thanks, Dave. This is exactly what I'm looking for. I'm also trying to decifer the formula to learn from your example. "Dave Peterson" wrote: I'm not sure I understand the layout of your data, but this will remove the leading 0's in A1: =MID(A1,SEARCH(LEFT(SUBSTITUTE(A1,"0",""),1),A1),2 55) Compass Rose wrote: Using Excel 2003 We manufacture products that have 4 properties, stored in columns B, C, D and E. I create a part number for each product by concatenating the data in the four columns. The property in Column D can be from 1 to 6 characters in length, so when I create the part number, I apply the following formula to the data in column D: RIGHT("00000"&D1,6) to ensure that all part numbers will be of the same length. When the part number is created, it is 13 characters long. What formula can I apply to the part number to extract the original property in column D, stripping away only the leading zeros? TIA David -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Strip leading zeros
To see how Dave's formula works, let's look at a step-by-step example. For
this example, we will assume A1 contains 0000980AB0765 (although the length and composition of the value is not important). SUBSTITUTE(A1,"0","") produces 98AB765 LEFT(SUBSTITUTE(A1,"0",""),1) produces 9 SEARCH(LEFT(SUBSTITUTE(A1,"0",""),1),A1) produces 5 which is the position of the first 9 in 0000980AB0765. The MID function then uses the 5 as its starting point in A1 (we know this the first character after all the zeroes) and returns up to the next 255 characters (it doesn't matter if the text is less than 255 characters... the 255 just needs to be larger than the longest text after the first non-zero characters which, for your setup, could have been 13). Rick "Compass Rose" wrote in message ... Thanks, Dave. This is exactly what I'm looking for. I'm also trying to decifer the formula to learn from your example. "Dave Peterson" wrote: I'm not sure I understand the layout of your data, but this will remove the leading 0's in A1: =MID(A1,SEARCH(LEFT(SUBSTITUTE(A1,"0",""),1),A1),2 55) Compass Rose wrote: Using Excel 2003 We manufacture products that have 4 properties, stored in columns B, C, D and E. I create a part number for each product by concatenating the data in the four columns. The property in Column D can be from 1 to 6 characters in length, so when I create the part number, I apply the following formula to the data in column D: RIGHT("00000"&D1,6) to ensure that all part numbers will be of the same length. When the part number is created, it is 13 characters long. What formula can I apply to the part number to extract the original property in column D, stripping away only the leading zeros? TIA David -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
add leading zeros | Excel Worksheet Functions | |||
Help with leading zero getting strip by Excel when saving to a .cs | Excel Discussion (Misc queries) | |||
Strip leading spaces from cell | Excel Worksheet Functions | |||
Leading zeros | Excel Discussion (Misc queries) | |||
save text field w/ leading zeros in .csv format & not lose zeros? | Excel Discussion (Misc queries) |