#### TheRedCardinal

##### Board Regular

- Joined
- Jul 11, 2019

- Messages
- 119

Not VBA related at this stage but I will be hoping to convert it into VBA in the long run.

I need to convert a number, which will be of indeterminate length, into a text string of fixed length. The length is 14 characters long.

To fill the length, I need to add leading zeros to the start of the number. So far I have come up with this:

Data Cell | B1 |

Value in Data Cell | 12345,67 |

Result Cell | C1 |

Formula in Result Cell | =REPT(0,14-LEN(TEXT(B1,"0.00")))&TEXT(B1,"0.00") |

<tbody>

</tbody>

The problem is the result I get include the decimal place (fairly obviously):

00000123456.70

But I can't figure out how to get it without the decimal place:

00000012345670

I can't use LEFT and MID functions because the "." moves around and in fact in case where there is no decimal place, isn't even there.

I have tried formatting the numbers to be two decimal places but while it looks right on the screen: 1234 when formatted to 2 decimal places looks like 1234.00, but converts to 1234, and 1234.5, when formatted to two decimal places looks like 1234.50, but the Text function returns 1234.5.

Thanks!