Text format for numbers to preserve trailing zeros

[KonEd]

Hi All,
I am a newcomer here. I found an old question which I am interested to.

Text format for numbers to preserve trailing zeros

I have a column of numeric values (####.##) that I need to convert to text. However when it is converted to text the trailing zeros after the decimal also need to be retained. These digits are going to be combined with other text fields in a single text string to create a TXT file to then...

www.mrexcel.com

I ask anybody to write a Sub file to apply this Function published there:
=IFERROR(IF(LEN(RIGHT(C1,LEN(C1)-FIND(".",C1)))<3,LEFT(C1&0&REPT(" ",10),10),LEFT(C1&REPT(" ",10),10)),LEFT(C1&".00"&REPT(" ",10),10))
Thanks in advance.

 

[Joe4]

Welcome to the Board!

I am not sure if that is necessary. You may be able to do it easier with the TEXT function.
But can you explain EXACTLY what you are trying to do, and show us a few examples of your data and expected output?

And when you say "write a Sub file", do you mean that you are looking for a VBA solution instead of an Excel formula?
What if it is a very simple Excel formula (not like the one you posted above)?

 

[RoryA]

If all you need is 2DP and a total length of 10 including padded spaces, then:

=LEFT(TEXT(A1,"0.00          "),10)

 

[KonEd]

Welcome to the Board!

I am not sure if that is necessary. You may be able to do it easier with the TEXT function.
But can you explain EXACTLY what you are trying to do, and show us a few examples of your data and expected output?

And when you say "write a Sub file", do you mean that you are looking for a VBA solution instead of an Excel formula?
What if it is a very simple Excel formula (not like the one you posted above)?

Thanks for your helpfulness

I have Excel tables containing thousands lines with text and number cells. Part of it is enclosed below. I need convert the table into a plain text file. Simple copy/paste method results in disordered layout of columns due to omitting of trailing zeros in number cells by default. So it gives a lot of manual work.

I am going to write a VBA Macro Sub to add necessary trailing zeros in a resulting file arising from separate number columns. After that I would combine all the text columns into a general text table using the Multiedit package.

I suppose to have some simple form in my Sub to insert the column name, its width and the number of digits.

 

[Joe4]

A solution like Rory's should do what you want, but if you want to do the same thing in VBA, take a look at the FORMAT function.
Take a look here for an explanation and examples: Format function (Visual Basic for Applications)

 

[KonEd]

An extremely easy solution was found due to a clever student Save As .txt Format Keeping Column Data Aligned - OzGrid Free Excel/VBA Help Forum.
In my case, after changing to Courier New it is sufficient copy/paste.
Thanks to All.