Farihat
Excel is a famous software to process many kinds of information into impactful action. Inserting various types of data, leading zeros in a cell is often omitted. If they are not converted to text, Excel does that. Because Excel considers the number as an integer and leading zeros as unnecessary data to store. Although the method is convenient for data storing, users usually input leading zeros in a cell to present IDs, codes, account numbers, and many others. Regular life digits like these prove this auto removal of leading zeros harmful. This article will guide you through ten methods to convert number to text with leading zeros in Excel and mitigate this issue. You will get step-by-step instructions, with a figurative description for all the methods.
📁 Download Excel File
Download the Excel file below.
Learn to Convert Number to Text with Leading Zeros in Excel Using These Nine Methods
The article is going to introduce nine methods to convert number to text in Excel with leading zeros. The methods will provide simple to complex solutions gradually. A simple solution involves the addition of an Apostrophe or changing the Format to Text, Special, or Custom. Complex ones will be covering functions such as BASE, TEXT, RIGHT, REPT – LEN, and CONCATENATE. Users will get this versatile walkthrough of these solutions to one of the fundamental problems.
1. Using Apostrophe
The easiest method to convert number to text with leading zeros in Excel is using Apostrophe. On the other hand, it is inconvenient to use in case of a wide range of records.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, you need to select the cell where you will add the leading zero(s). Before starting to input, you should use Apostrophe, which is the (‘) character on your keyboard.
- Now, you need to add the number along with the leading zeros just as the number is addressed.
- Next, Excel will show you an error sign, where you should select Ignore Error.
- You have to repeat the process manually for every cell in the worksheet.
📕 Read More: 4 Ways to Convert Number to Text and Keep Trailing Zeros in Excel
2. Utilizing Text Format
You can declare column(s) to show data in Text format. Keeping numbers, with leading zero(s) at Number or General format, will automatically delete leading zeros, right after inserting the number.
⬇️⬇️ STEPS ⬇️⬇️
- To start with the method, you need to format the column (or row or cells) from General to Text from the number format tool under the Home tab.
- After that, you can write numbers with as much as leading zeros as needed. Excel will not remove the zeros.
3. Applying Special Format
The special format is a go-to method for formatting ZIP codes, phone numbers, social security numbers and many other country-based numerical formats. This option allows as many as leading zeros for numbers. You don’t need to enable it cell by cell.
⬇️⬇️ STEPS ⬇️⬇️
- The number format tool has an extension. As shown in the picture, you need to select a column (or rows or cells). Along with that, you will get the number format dialogue box from the extension option.
- You should start this step by choosing the Special Followed by the category, you can choose your location to get available special formats. As portrayed in the picture, we have chosen English (United States) which gets us to select Social Security Number as the data type.
- After finalizing the choice, Excel will format the selected region. Eventually, you can see all the data in the format of the United States Social Security Number when you call the data from previous cells.
4. Employing Custom Format
You can add as many leading zeros using the Custom format, declaring the digit allocation for a cell or cells or rows or columns. This command will set the trailing digits with the non-zero values and complete the remaining leading digits with zero(s).
⬇️⬇️ STEPS ⬇️⬇️
- Similar to previous format methods, the number format dialogue box is also the start of this method.
- You should select the Custom category. As the picture shows, you will need to Type zeros in the number that you want all your numbers to show.
- Completing the choice, the entire selected region will be formatted. You can include all by the following formula.
- Copy it through the column.
5. Using BASE Function
In the case of converting numerical data to text with leading zeros, we can make use of the BASE function as well. The BASE function has radix 2, 7, 10, and 16 for binary, octal, decimal, and hexadecimal numbers respectively. Declaring a function with the previous cell ID, radix, and the digits you would like to view your number with.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, we need to select the cell where you will store the corrected data. There, you can use the following function.
- As shown in the image below, you can see the function will get your desired output.
- In the second step, we need to copy the formula of E6 and drag the action to E8. You should get a visual like the following with this method.
📕 Read More: How to Convert Number to Text with 2 Decimal Places in Excel
6. Using TEXT Function
In the case of converting numerical data to text with leading zeros, you can try the TEXT function. You should declare the TEXT function with the previous cell ID and zeros under inverted commas as much as the digits you would like to view your number with.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, you should select the cell where you will store the corrected data. You can use the following function there where C6 is the reference cell and required digits are presented by eight zeros.
- As shown in the image below, you can see the function will get your desired output.
- In the second step, we need to copy the formula of E6 and drag the action to E8. You should get a visual like the following with this method.
📕 Read More: 3 Ways to Convert Number to Text with Commas in Excel
7. Employing RIGHT Function
The RIGHT function is a digit setting function. You can set the data format or digit allocation, followed by mentioning the exact number.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, you will select the cell where you will store the corrected data. You need to write the following function in the cell.
- As shown in the image below, you can see the function will get your desired output whereas “00000000”&C6 is the data format and 8 is the digit count.
- In the second step, you should copy the formula of E6 and drag the action till E8. You should get a visual like the following with this method.
8. Merging REPT and LEN Functions
The REPT function is used to repeat a string multiple times. The LEN function returns the number of total characters in a text string. Using the REPT function on the LEN function returns the repeated action on the number that LEN returns.
⬇️⬇️ STEPS ⬇️⬇️
- To begin with, you need to select the cell where you will store the corrected data. You can use the following function to correct the number from C6
- As shown in the image below, you can see the function will get your desired output. Here LEN is selected on C6. Upon the return on character number of C6, it is subtracted from 8, so that zero can be repeated as many times as the leading digits from the subtraction.
- In the second step, we need to copy the formula of E6 and drag the action to E8. You should get a visual like the following with this method.
9. Employing CONCATENATE Function
In order to convert numerical data to text with leading zeros, we can make use of the CONCATENATE function as well. It joins two text strings from different cells.
⬇️⬇️ STEPS ⬇️⬇️
- In the beginning, you should select the cell where you will store the corrected data. There. You can follow the function below
- As shown in the image below, you can see that the function will get your desired output.
- In the second step, we need to copy the formula of E6 and drag the action to E8. You should get a visual like the following with this method.
- As you can notice, unlike other methods, CONCATENATE doesn’t maintain any specific digit count, and only adds strings as commanded. Moreover, it should be avoided if the data range isn’t of the same character length.
📝 Takeaways from This Article
The article allows the readers to understand the varieties of options available to convert numbers to text with leading zeros.
📌 Using apostrophe before numbers with leading zeros is a common practice.
📌 You can get an entire selection to show number as the way written. For that, changing the Number format to the Text format is the easiest.
📌 Special format ensures the exact format of the chosen entity. It can be code, phone numbers, etc.
📌 Custom format is applicable when the template isn’t available and a specific digit count is set.
📌 By the BASE function, you can arrange the dataset as a specific numerical system. It comes with a specific digit count.
📌 TEXT function encloses cell data to a declared digit count.
📌 RIGHT function is used to command data from the trailing side. Setting the provided data on the trailing, the RIGHT function can fill the remaining with zeros.
📌 REPT function and LEN function combination is one of the complex methods. However, it returns the desired result with precision.
📌 CONCATENATE helps to join a specific amount of leading zeros with another data string. Although easy and effective, it might create a misconception about the data
Conclusion
To convert numbers to text with leading zeros, Apostrophe, format changes to Text, Special, Custom, or application of functions such as BASE, TEXT, RIGHT, REPT–LEN, and CONCATENATE can be used as per the article. In case of further queries, readers are requested to leave a comment for the author. The author will try their best to come up with a suitable solution. Follow ExcelDen to get more access to solutions regarding your Excel problems.
