Adding leading Zero to single digit HELP

[jambog]

I am trying to make a column of numbers have a leading zero on all single digit numbers. (01 instead of 1)

I use formula =IF(A1<10,"0")&A1

Which works, but leaves FALSE in front of all numbers 10 and higher. I’m sure there is a way to avoid getting FALSE, but I tried it the novice way.

I copied the column , paste only values in new column and Find and Replace all occurrences of FALSE with nothing. The find and replace said it replaced 1700 values, but it did no such thing.

I also tried finding and replacing the single digits from original column i.e find 1 replace with 01 matching entire cell contents. Again it said it replaced X number of values, but it didn’t actually replace, they all still say 1. I have tried changing my format of column to general, text, number, etc. Still no replace even though it says it did. Find and Replace works on other columns, just not adding zero to number even if column is formatted as text or general instead of number.

The native format was .xls, but have also saved it as .xlsx with no luck. Using Excel 2010.

Either helping me complete formula so that no FALSE is returned or using Find and Replace would help. Thanks.

[Tsjallie]

This formula won't work. Just set the format (custom) of the cell(s) to 00.

[etaf]

do you want to keep the value as a number or are you adding the 0 as text

if a number
choose the range
right click
format
custom format
enter
00

now you should get
01
02
03

etc

[popipipo]

Please Login or Register  to view this content.

Or try this

[eibi]

I propose

Please Login or Register  to view this content.

[jambog]

Please Login or Register  to view this content.

Or try this

I think this is the best solution to my problem. Thanks!