Different methods of adding Zero before number in excel and there benefits

There are different method of adding zero in front of number in excel but all methods has different uses below here I am defining uses of different methods

a) Whether you use the TEXT formula or the format cell option – the functionality is exactly the same. The second part of the text formula is a number format so for example you can also use TEXT with more advanced number formats like =TEXT(A10,”#,##0.00_);(#,##0.00);-_)”) to good effect. The question is whether you want your number stored as a number or as text?

b) Most people replying to this thread obviously cannot understand why you’d ever want a number formatted as text; but there are some important uses of Excel where you DO need numbers formatted as text, particularly in the area of interfacing. i.e. getting system A to speak to system B. Examples I have encountered in the past include consolidation systems, management reporting systems, bank payment files, EDI invoicing, initial balance loads to a new accounting system, payroll systems, etc. Most of these systems will speak to Excel or export/import text either as a simple flat file or a CSV which can be opened, edited and saved by Excel. Where you would absolutely need numbers formatted as text with leading zeros is where the importing system doesn’t accept CSV or tab delimited text files and requires instead a simple text file with characters in specific positions meaning specific things. This is particularly important with bank payment files as a misplaced digit will mean you may pay the wrong amount or wrong person. [Obviously using Excel for payment file interfacing is not to be recommended as a long term solution.]
c) Excel stores numbers as a data type Double not as exact currency amounts. This means when transferring financial data between systems using Excel you often get trailing rogue decimal places. The only way to absolute ensure you get no errors in the importing system is to export numbers as ASCII text. It beats looking for the one line with the 0.001 that is stopping your thousand line journal from posting in the receiving system.
d) Contrary to what has been said previously in this thread it is not only possible, but very straightforward, to add numbers up that are formatted as text in Excel. You just need a basic array formula of the type {=SUM(VALUE(A1:A10))}. [If you have not used array formulas before, you don’t type the{} brackets you enter =SUM(VALUE(A1:A10)) and then hit CTRL+SHIFT+ENTER] The formula I have given above would add up both numbers and numbers formatted as text in the range A1:A10.
e) There are MUCH sexier and useful uses for the TEXT function and custom number formulas than adding leading zeros to the start of a number. To my mind the most useful of which is dynamic captioning in an Excel report. Consider for example the following more useful formula:
=”Revenue for “&TEXT(D1,”mmmm yyyy”)&” is “&TEXT((A19-B19)/B19,CHAR(34)&”up “&CHAR(34)&”0.0%;”&CHAR(34)&”down “&CHAR(34)&”0.0%;”&CHAR(34)&”exactly”&CHAR(34))&” on budget.”
Where A19 has your actual revenue, B19 has your budgeted revenue and cell D1 has the date. Char(34) is “, which is necessary because the custom number formula as part of the second TEXT formula above has words in it.
Set this kind of thing up once and you never need to type simple variance texts again.