Add leading zero to this cell content in Excel

Question

Suppose I have this content 8.XY in an Excel cell. I would like to add leading zeros such that it looks like 0008.XY. The converted value will always have 4 digits in front.

Other examples are 78.XY becomes 0078.XY. 188.XY becomes 0188.XY. 1123.XY remains 1123.XY. What Excel formula can I use to achieve this?

Answer 1

There are apparently more than one way to crack an egg, as evidenced by the number of working answers.

Another way, again assuming your string to be checked is in cell A1 could be to use the CHOOSE function which I prefer over nested IFs because of its clarity.

=IFERROR(CHOOSE(FIND(".",A1),"0000","000","00","0",""),"")&A1

IFERROR is used in case the string has five or more characters before the period.

CHOOSE selects a value or action to perform based in the index number in the first function parameter.

Answer 2

Another Method to Achieve

Assuming your strings starts with A1 and A2 and so on (Please refer the snap below).

Apply the below formula in B1 and drag down

=REPT("0",4-(SEARCH(".",A1)-1))&MID(A1,SEARCH(".",A1)-(SEARCH(".",A1)-1),LEN(A1))

Answer 3

Based on your statement that the number will always have 4 digits in front of the decimal.

=right("000"&A1,7)

The above also assumes you will only have 2 characters after the decimal point.

If you have the possibility of having more than 4 character in front of the decimal point, you could use the following:

=if(FIND(".",A1)<5,right("000"&A1,7),A1)

Again this assumes only 2 characters after the decimal point.

IF you are just interested in displaying your integer with leading 0 and a trailing .XY, then you could format your cells to display that. In order to achieve this follow these steps.

1) Select the a cell or the range of cells with the data you want changed.
2) While those cell are selected, go to the "HOME" ribbon and select the drop down menu adjacent to GENERAL.
3) From the menu that is displayed, select "More Number Formats".
4) In the window that comes up select "Custom" in the Category window.
5) In in the Format Bar enter: 0000".XY" .
6) Copy cell formatting to other cells as required.

Answer 4

Assuming your string to be checked is in cell A1, use

=IFERROR(REPT("0",5-FIND(".",A1)),"")&A1

The IFERROR function is used in case your string has more than 4 characters before the period.

Answer 5

The following formula will do what you want by essentially converting your data to a string, where I have used G2 as the cell with your data in. =IF(FIND(".",G2,1)-1=4,G2,IF(FIND(".",G2,1)-1=3,"0"&G2,IF(FIND(".",G2,1)-1=2,"00"&G2,IF(FIND(".",G2,1)-1=1,"000"&G2,"0000"&G2))))