| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
adding leading zero in Excel
How do I add zeros to numbers in a cell? ex: R1,R12,R152 to R001,R012,R152
This needs to be done in many cells. Thanks |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
adding leading zero in Excel
=LEFT(A1,1) & TEXT(RIGHT(A1,LEN(A1)-1),"000")
-- Gary''s Student - gsnu200744 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
adding leading zero in Excel
I could not get this to work.
"Gary''s Student" wrote: =LEFT(A1,1) & TEXT(RIGHT(A1,LEN(A1)-1),"000") -- Gary''s Student - gsnu200744 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
adding leading zero in Excel
That's O.K. We can solve this.
In A1 thru A3 I entered: R1 R12 R152 I put my formula in B1 thru B3 and see: R1 R001 R12 R012 R152 R152 What do you see? -- Gary''s Student - gsnu200744 "ENFGDC4S" wrote: I could not get this to work. "Gary''s Student" wrote: =LEFT(A1,1) & TEXT(RIGHT(A1,LEN(A1)-1),"000") -- Gary''s Student - gsnu200744 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
adding leading zero in Excel
This is where my problem is. Here is an example of one of the cells:
C5,C16,C30,C38,C54,C91,C98,C136,C147 I can have a few hundred of these alpha numeric characters in one cell. Thanks for your time. "Gary''s Student" wrote: That's O.K. We can solve this. In A1 thru A3 I entered: R1 R12 R152 I put my formula in B1 thru B3 and see: R1 R001 R12 R012 R152 R152 What do you see? -- Gary''s Student - gsnu200744 "ENFGDC4S" wrote: I could not get this to work. "Gary''s Student" wrote: =LEFT(A1,1) & TEXT(RIGHT(A1,LEN(A1)-1),"000") -- Gary''s Student - gsnu200744 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
adding leading zero in Excel
O.K.
You see my mis-understanding. I can solve this with visual basic. Check back later -- Gary''s Student - gsnu200744 "ENFGDC4S" wrote: This is where my problem is. Here is an example of one of the cells: C5,C16,C30,C38,C54,C91,C98,C136,C147 I can have a few hundred of these alpha numeric characters in one cell. Thanks for your time. "Gary''s Student" wrote: That's O.K. We can solve this. In A1 thru A3 I entered: R1 R12 R152 I put my formula in B1 thru B3 and see: R1 R001 R12 R012 R152 R152 What do you see? -- Gary''s Student - gsnu200744 "ENFGDC4S" wrote: I could not get this to work. "Gary''s Student" wrote: =LEFT(A1,1) & TEXT(RIGHT(A1,LEN(A1)-1),"000") -- Gary''s Student - gsnu200744 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
adding leading zero in Excel
I guess we should further clarify... is the part in front of the number
always a SINGLE letter character? Is the number part always going to be three digits long? Any other info we should know? Rick "ENFGDC4S" wrote in message ... This is where my problem is. Here is an example of one of the cells: C5,C16,C30,C38,C54,C91,C98,C136,C147 I can have a few hundred of these alpha numeric characters in one cell. Thanks for your time. "Gary''s Student" wrote: That's O.K. We can solve this. In A1 thru A3 I entered: R1 R12 R152 I put my formula in B1 thru B3 and see: R1 R001 R12 R012 R152 R152 What do you see? -- Gary''s Student - gsnu200744 "ENFGDC4S" wrote: I could not get this to work. "Gary''s Student" wrote: =LEFT(A1,1) & TEXT(RIGHT(A1,LEN(A1)-1),"000") -- Gary''s Student - gsnu200744 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
adding leading zero in Excel
I can solve this with visual basic.
I'll save you the trouble.<g If there is only one leading letter character for each "field" in the cell and if the number part always needs to be three digits long, then this sub should work... Sub AddZeroes() Dim C As Range Dim Fields() As String For Each C In Selection Fields = Split(C.Value, ",") For X = 0 To UBound(Fields) Fields(X) = Left$(Fields(X), 1) & _ Format$(Mid$(Fields(X), 2), "000") Next C.Value = Join(Fields, ",") Next End Sub To ENFGDC4S: ============= All you have to do is right-click the sheet tab at the bottom, and "View Code"; then copy/paste the above routine into the sheet's code window. Once you have done that, go back to the spreadsheet and select the cells you want to apply this routine to and then press Alt+F8 and run the AddZeroes macro from the dialog box that appears. Rick |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
adding leading zero in Excel
The alpha character will be a single character and followed by up to 3
numerals. There is a posibility of up to 4 numerals but does not happen much. 3 numerals will be fine I would only need the leading zeros if the numeral is from 1 - 99, for example, C3,C34 would be C003,C034. There is also a comma after each item. These are Reference Designators on a printed wiring board and are used in the parts list for idenification. "Rick Rothstein (MVP - VB)" wrote: I guess we should further clarify... is the part in front of the number always a SINGLE letter character? Is the number part always going to be three digits long? Any other info we should know? Rick "ENFGDC4S" wrote in message ... This is where my problem is. Here is an example of one of the cells: C5,C16,C30,C38,C54,C91,C98,C136,C147 I can have a few hundred of these alpha numeric characters in one cell. Thanks for your time. "Gary''s Student" wrote: That's O.K. We can solve this. In A1 thru A3 I entered: R1 R12 R152 I put my formula in B1 thru B3 and see: R1 R001 R12 R012 R152 R152 What do you see? -- Gary''s Student - gsnu200744 "ENFGDC4S" wrote: I could not get this to work. "Gary''s Student" wrote: =LEFT(A1,1) & TEXT(RIGHT(A1,LEN(A1)-1),"000") -- Gary''s Student - gsnu200744 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
adding leading zero in Excel
Okay, see my response to Gary''s Student where the code I posted anticipated
the answer you gave. Rick "ENFGDC4S" wrote in message ... The alpha character will be a single character and followed by up to 3 numerals. There is a posibility of up to 4 numerals but does not happen much. 3 numerals will be fine I would only need the leading zeros if the numeral is from 1 - 99, for example, C3,C34 would be C003,C034. There is also a comma after each item. These are Reference Designators on a printed wiring board and are used in the parts list for idenification. "Rick Rothstein (MVP - VB)" wrote: I guess we should further clarify... is the part in front of the number always a SINGLE letter character? Is the number part always going to be three digits long? Any other info we should know? Rick "ENFGDC4S" wrote in message ... This is where my problem is. Here is an example of one of the cells: C5,C16,C30,C38,C54,C91,C98,C136,C147 I can have a few hundred of these alpha numeric characters in one cell. Thanks for your time. "Gary''s Student" wrote: That's O.K. We can solve this. In A1 thru A3 I entered: R1 R12 R152 I put my formula in B1 thru B3 and see: R1 R001 R12 R012 R152 R152 What do you see? -- Gary''s Student - gsnu200744 "ENFGDC4S" wrote: I could not get this to work. "Gary''s Student" wrote: =LEFT(A1,1) & TEXT(RIGHT(A1,LEN(A1)-1),"000") -- Gary''s Student - gsnu200744 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
adding leading zero in Excel
Very nice
Thank you for introducing me to JOIN() -- Gary''s Student - gsnu200744 "Rick Rothstein (MVP - VB)" wrote: I can solve this with visual basic. I'll save you the trouble.<g If there is only one leading letter character for each "field" in the cell and if the number part always needs to be three digits long, then this sub should work... Sub AddZeroes() Dim C As Range Dim Fields() As String For Each C In Selection Fields = Split(C.Value, ",") For X = 0 To UBound(Fields) Fields(X) = Left$(Fields(X), 1) & _ Format$(Mid$(Fields(X), 2), "000") Next C.Value = Join(Fields, ",") Next End Sub To ENFGDC4S: ============= All you have to do is right-click the sheet tab at the bottom, and "View Code"; then copy/paste the above routine into the sheet's code window. Once you have done that, go back to the spreadsheet and select the cells you want to apply this routine to and then press Alt+F8 and run the AddZeroes macro from the dialog box that appears. Rick |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
adding leading zero in Excel
Thank you for introducing me to JOIN()
You are quite welcome. As the companion function to Split, it is really a very nice function to have in the arsenal. Rick "Rick Rothstein (MVP - VB)" wrote: I can solve this with visual basic. I'll save you the trouble.<g If there is only one leading letter character for each "field" in the cell and if the number part always needs to be three digits long, then this sub should work... Sub AddZeroes() Dim C As Range Dim Fields() As String For Each C In Selection Fields = Split(C.Value, ",") For X = 0 To UBound(Fields) Fields(X) = Left$(Fields(X), 1) & _ Format$(Mid$(Fields(X), 2), "000") Next C.Value = Join(Fields, ",") Next End Sub To ENFGDC4S: ============= All you have to do is right-click the sheet tab at the bottom, and "View Code"; then copy/paste the above routine into the sheet's code window. Once you have done that, go back to the spreadsheet and select the cells you want to apply this routine to and then press Alt+F8 and run the AddZeroes macro from the dialog box that appears. Rick |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
adding leading zero in Excel
The sub worked great! Thank you for your time and effort.
Eric "Rick Rothstein (MVP - VB)" wrote: Okay, see my response to Gary''s Student where the code I posted anticipated the answer you gave. Rick "ENFGDC4S" wrote in message ... The alpha character will be a single character and followed by up to 3 numerals. There is a posibility of up to 4 numerals but does not happen much. 3 numerals will be fine I would only need the leading zeros if the numeral is from 1 - 99, for example, C3,C34 would be C003,C034. There is also a comma after each item. These are Reference Designators on a printed wiring board and are used in the parts list for idenification. "Rick Rothstein (MVP - VB)" wrote: I guess we should further clarify... is the part in front of the number always a SINGLE letter character? Is the number part always going to be three digits long? Any other info we should know? Rick "ENFGDC4S" wrote in message ... This is where my problem is. Here is an example of one of the cells: C5,C16,C30,C38,C54,C91,C98,C136,C147 I can have a few hundred of these alpha numeric characters in one cell. Thanks for your time. "Gary''s Student" wrote: That's O.K. We can solve this. In A1 thru A3 I entered: R1 R12 R152 I put my formula in B1 thru B3 and see: R1 R001 R12 R012 R152 R152 What do you see? -- Gary''s Student - gsnu200744 "ENFGDC4S" wrote: I could not get this to work. "Gary''s Student" wrote: =LEFT(A1,1) & TEXT(RIGHT(A1,LEN(A1)-1),"000") -- Gary''s Student - gsnu200744 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Adding a leading zero to a SSN | Excel Discussion (Misc queries) | |||
| Adding "0" as leading digits into cells in Excel | Excel Worksheet Functions | |||
| Excel Leading Zeros | Excel Discussion (Misc queries) | |||
| leading zeros in excel | Excel Discussion (Misc queries) | |||
| Adding Leading Zeros to Text | Excel Discussion (Misc queries) | |||
Linear Mode
