SOLVED

OS windows 2010 windows 365 excel

Copper Contributor

I have a column of numbers of 2 and 3 digits, I like to have leading zeros for the 2 digit

45

65

789

Need to add leading a zero to 45 and 65

11 Replies

@Greg_Dupreehteeter 

If it is for display only, apply the custom number format 000 to the column.

If you want the leading zero to be part of the value, do the following.

Let's say the numbers are in A2:A100.

If necessary, insert a column to the right of column A.

In B2, enter the formula =TEXT(A2,"000")

Fill down to B100.

 

Optionally, select B2:B100.

Copy the range, then paste as values.

You can then remove column A.

@Hans Vogelaar 

Hello, I really appreciate your answer and that worked. Just not sure how to paste on down the column with the formula.

 

And what if I wanted to paste 2 digit numbers so it will autofill to 3 digits verses having a predefined text spreadsheet?

thanks

Greg Dupree@hteeter

@Greg_Dupreehteeter 

Drag down the fill handle in the lower right corner of the first cell with the formula to copy the formula to the cells below.

If you want to convert 2 digits automatically to 3 digits, that would require VBA code. Would that be acceptable? (macros won't work in the online, iOS and Android versions of Excel)

Hi,
no its not converting, just adding the leading zero to 2 digit character
I'm thinking I would key to this formula, I have 50 cells
paste numbers into column 1 , separate each with comma and then transpose to 10 numbers per row. Not sure how to transpose the rows
=TEXT(A1:A50",000")

@Greg_Dupreehteeter 

I'm afraid you've lost me.

sorry about the confusion and thanks for the help
I have hundreds of numbers; 2 and 3 digits, to run my program correctly, each digit must be 3 digits and separated by commas; like 002,006,012,455,217,009, etc.
so my text file have hundreds; 3,2,122,08, etc. I would like to paste them in excel, add leading zeros for 3 digits, but I can only have 10 numbers per line in the program.
can the formula apply to a row?

@Greg_Dupreehteeter 

Could you attach a sample text file that you want to be converted to Excel?

@Hans Vogelaar 

Store

3

4

6

11

19

21

23

25

26

27

28

29

30

33

34

35

37

38

39

43

45

46

51

58

61

63

64

66

68

69

78

80

81

82

83

85

86

87

88

90

91

92

96

99

100

103

104

112

115

117

118

120

121

122

124

127

129

131

132

133

135

136

137

138

139

142

147

149

152

155

157

158

160

161

162

165

166

167

168

171

172

173

174

175

176

177

178

179

181

182

190

191

best response confirmed by allyreckerman (Microsoft)
Solution

@Greg_Dupreehteeter 

See the attached workbook - it assumes that you have Office 2019 or Microsoft 365.

amazing! that's really neat

How can I see the formula you applied?
I see the formula now when clicking the offset cell of it.

thanks a lot. I tried it and it works great!
1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@Greg_Dupreehteeter 

See the attached workbook - it assumes that you have Office 2019 or Microsoft 365.

View solution in original post