Forum Discussion

Ross Stewart's avatar
Ross Stewart
Copper Contributor
May 02, 2018

A problem retaining leading zeros in Excel 2010

 

 

I have a simple spread sheet containing customer numbers which variously range from 4 to 968 (ie up to 3 digits), and in Excel, normally,  no leading zeroes are retained.

By changing the column format  to CUSTOM with 000, all leading zeroes are now shown.

So far so good.

But now I need to concatenate each customer number with the same 5-chtr text, being “/1000”.  So that I’ll end up with, in each single result cell   004/1000, 006/1000, 019/1000, 023/1000,  etc etc.

Bust when I do the concatenate, the leading zeroes are dropped from the customer portion, giving me  4/1000, 6/1000, 19/1000, 23/1000, etc , which I don’t want to happen.

Help, please!

  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor

    Hi Ross,

     

    Supposing that the customer number is in cell A1, please use this formula:

    =TEXT(A1,"000")&"/1000"

     

    Regards

Resources