adding leading zero in Excel

[Guest]

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

 

[Guest]

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

 

[Guest]

I could not get this to work.

 

[Guest]

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?

 

[Guest]

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.

 

[Guest]

O.K.

You see my mis-understanding.

I can solve this with visual basic.

Check back later

 

[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

 

[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

 

[Guest]

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\)]

Okay, see my response to Gary''s Student where the code I posted anticipated
the answer you gave.

Rick

 

[Guest]

Very nice


Thank you for introducing me to JOIN()

 

[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

 

[Guest]

The sub worked great! Thank you for your time and effort.
Eric