Forum Discussion
Ross Stewart
May 02, 2018Copper Contributor
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!
- Ross StewartCopper ContributorThat's magic. And yes, works like a dream. Thank you.
- Haytham AmairahSilver Contributor
Hi Ross,
Supposing that the customer number is in cell A1, please use this formula:
=TEXT(A1,"000")&"/1000"
Regards