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

adding leading zero in Excel
 

=LEFT(A1,1) & TEXT(RIGHT(A1,LEN(A1)-1),"000")

--
Gary''s Student - gsnu200744

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

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

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

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

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

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

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

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

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

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

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