E
ENFGDC4S
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
This needs to be done in many cells. Thanks
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
Gary''s Student
=LEFT(A1,1) & TEXT(RIGHT(A1,LEN(A1)-1),"000")
E
ENFGDC4S
I could not get this to work.
G
Gary''s Student
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?
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?
E
ENFGDC4S
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.
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.
G
Gary''s Student
O.K.
You see my mis-understanding.
I can solve this with visual basic.
Check back later
You see my mis-understanding.
I can solve this with visual basic.
Check back later
R
Rick Rothstein \(MVP - VB\)
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
always a SINGLE letter character? Is the number part always going to be
three digits long? Any other info we should know?
Rick
R
Rick Rothstein \(MVP - VB\)
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
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
E
ENFGDC4S
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.
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.
R
Rick Rothstein \(MVP - VB\)
Okay, see my response to Gary''s Student where the code I posted anticipated
the answer you gave.
Rick
the answer you gave.
Rick
G
Gary''s Student
Very nice
Thank you for introducing me to JOIN()
Thank you for introducing me to JOIN()
R
Rick Rothstein \(MVP - VB\)
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
You are quite welcome. As the companion function to Split, it is really a
very nice function to have in the arsenal.
Rick
E
ENFGDC4S
The sub worked great! Thank you for your time and effort.
Eric
Eric