Format Column to add leading zero to numbers as well as accept text

[Rusty2468]

I have a column like the following:

1
20
3
C
C50
C 40

I wish to format it so that it shows:

01
20
03
C
C50
C 40

How can I do this in a macro?

Many thanks fort your help.

 

[footoo]

Turn on the macro recorder, select the column, format as 00, turn off recorder

 

[sandy666]

maybe try:

[Table="width:, class:head"][tr=bgcolor:#000000][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

1​

[/td][td]

1​

[/td][td]01[/td][td]B1: =IF(ISTEXT(A1),A1,BASE(A1,10,2))[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

2​

[/td][td]

20​

[/td][td]20[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

3​

[/td][td]

3​

[/td][td]03[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

4​

[/td][td]C[/td][td]C[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

5​

[/td][td]C50[/td][td]C50[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

6​

[/td][td]C 40[/td][td]C 40[/td][td][/td][/tr]
[/table]

 

[Rusty2468]

maybe try:

[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] "]
[TH][/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​

[/TH]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​

[/TD]
[TD]

1​

[/TD]
[TD]01[/TD]
[TD]B1: =IF(ISTEXT(A1),A1,BASE(A1,10,2))[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​

[/TD]
[TD]

20​

[/TD]
[TD]20[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​

[/TD]
[TD]

3​

[/TD]
[TD]03[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​

[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​

[/TD]
[TD]C50[/TD]
[TD]C50[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​

[/TD]
[TD]C 40[/TD]
[TD]C 40[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I forgot to mention that I'm using Excel 2010 - base function is not available in my version.

 

[sandy666]

no problem: =IF(ISTEXT(A1),A1,TEXT(A1,"00"))

 

[Rusty2468]

Thank you Sandy.

 

[sandy666]

You are welcome

 

[xenou]

Without using a macro, also possible is to apply a custom format:
0#;-0#;0#;@

about custom formats:
https://exceljet.net/custom-number-formats

Note that custom formats (like all number formats) do not affect underlying values, only the way they are displayed.