How To Standardise All The Numbers To FIVE Digits?
Jason Khoo Biz Analytics, Data Visualization, Excel, Power BI
Microsoft Certified Trainer and consultant | MS Certified Power BI Data Analyst Associate | ACTA
It was a simple question,
I have a column with numbers and I want to standardize the length of the number to 5 digits.
The easiest response is to format the column to text and re-type in the numbers.
But I have one thousand numbers. You can still do the same if you have the time. What if you do not have the time. Not a big problem. Just use the worksheet function below, assuming that the first number is in cell A2.
=text(A2,"00000")
I tried and it didn't work. The number is 12A. When I typed in the formula, it still gives me 12A. Excel is not reliable. Got bugs. I will usually laugh at these people because they do not know that they are the one who has a problem. Here's why:
12A is not a number!
12A is a text. So you cannot use the above formula. So how?
Some people will think it is too complicated for them and go back to the first method. They will never learn.
If you have a column of text (12A) and numbers (12), you can use this formula.
=Rept("0",5-len(A2))&A2
If you want to know more about this formula, comment below and say I want to know more. I will add more if I there are 50 comments on "I want to know more"
★ Performance Leadership ★ APAC Market Expansion ★ Business Management ★ Business Development ★ Key Account Management ★ Customer Acquisition ★ Upselling ★
4 年I want to know more, Jason!
Investor Relations I Financial Communications I Fundraising I US IPO
4 年Format cell as custom entry?
A seasoned leader with excellent track record in fostering an environment where people are inspired to deliver results.
4 年Excel expert Jason Khoo (L.I.O.N) !