How to fill in zero.

  • Thread starter Thread starter John
  • Start date Start date
J

John

anyone know how to fill in 0 between N and the #.(Formula or Macro)
N001----------->N00001
N002----------->N00002
N003----------->N00003
N004----------->N00004
N005----------->N00005
N006----------->N00006
N007----------->N00007
N008----------->N00008
N009----------->N00009
N0010---------->N00010
N0011---------->N00011
N0012---------->N00012
N00125--------->N00125
N00126--------->N00126
 
If all data will start with N:
="N"&REPT("0",6-LEN(A1))&RIGHT(A1,LEN(A1)-1)

If the start letter is variable:
=LEFT((A1),1)&REPT("0",6-LEN(A1))&RIGHT(A1,LEN(A1)-1)
 
hi
this will work for you but not in all of your examples. in some you are
adding 2 zeros and others, 1 zero and in others, no zeros. so you will have
to adjust the following to accommodate the variances.
=LEFT(K5,2)&"00"&RIGHT(K5,1)
the above adds 2 zeros and pick 1 number from the end ie
N009----->N00009

regards
FSt1
 
One more:
=LEFT(A1)&TEXT(MID(A1,2,5),"00000")

anyone know how to fill in 0 between N and the #.(Formula or Macro)
N001----------->N00001
N002----------->N00002
N003----------->N00003
N004----------->N00004
N005----------->N00005
N006----------->N00006
N007----------->N00007
N008----------->N00008
N009----------->N00009
N0010---------->N00010
N0011---------->N00011
N0012---------->N00012
N00125--------->N00125
N00126--------->N00126
Click to expand...
 
Hi
I had copy and paste this ="N"&REPT("0",6-LEN(A1))&RIGHT(A1,LEN(A1)-1) at
N001, it change to a 0 only. can you tell how to use this formula.
 
The formula assumes that N001 is in cell A1. If it is in a different cell,
change both references from A1 to that cell.
 
guys
thank's for the help, I got it work now.

John said:
Hi
I had copy and paste this ="N"&REPT("0",6-LEN(A1))&RIGHT(A1,LEN(A1)-1) at
N001, it change to a 0 only. can you tell how to use this formula.
Click to expand...
 
Back
Top