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:

	A​	B​	C​
1​	1​	01	B1: =IF(ISTEXT(A1),A1,BASE(A1,10,2))
2​	20​	20
3​	3​	03
4​	C	C
5​	C50	C50
6​	C 40	C 40

 

[Rusty2468]

maybe try:

	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​	1​	01	B1: =IF(ISTEXT(A1),A1,BASE(A1,10,2))
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​	20​	20
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​	3​	03
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​	C	C
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​	C50	C50
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​	C 40	C 40

<tbody>
</tbody>

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.