Bhubon Costa
You frequently need to add text with numbers to specify its characters and purposes. Microsoft Excel doesn’t offer a built-in function or formula for the objective. But you can surely do it with the help of some options and with the combination of some formulas. In this tutorial, we will demonstrate how to custom cell format number with text in Excel.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
4 Suitable Ways to Custom Cell Format Number with Text in Excel
In the sections that follow, we will use four different approaches to add text with numbers to customize a cell format. At first, we’ll utilize the Format Cells box. Later on, we’ll use formulas, the CONCATENATE function, and the TEXT function. A sample data set is provided to make you understand what is the purpose of our task.
1. Use Formal Cells Box to Custom Number with Text in Excel
To customize numbers with text by using the Format Cells box, flow the steps below.
Step 1: Selection of the Cells
- Select all the cells.
Step 2: Open the Format Cells Box
- Press Ctrl + 1 to open the Format Cells.
- Click on the Custom.
- Select or type any format you need.
Step 3: Add Text in the Type Box
- In the Type box, write any text closed in the (“ ”). For example, we will add USD in the end with the following formula. #,##0 “USD”
Step 4: Get the Results
- Finally, press Enter to get the results.
Read More: Excel Custom Number Format Multiple Conditions
2. Apply a Formula to Custom Cell Format Number with Text in Excel
By applying an ampersand operator (&) with a formula, you can format a cell number with text. Simply, follow the outlined steps.
Step 1: Insert a Formula
- Firstly, select any cell.
- Use an ampersand (&).
- Type the text enclosed with the inverted comma (“USD”).
Step 2: Get the Result
- Press Enter to see the first result.
Step 3: Copy the Formula to the Blank Cells
- Therefore, use the AutoFill to fill up the blank cells in the column.
Related Content: How to Round Numbers in Excel Without Formula (3 smart ways)
Similar Readings
- Excel Round to Nearest 100 (6 Quickest Ways)
- Excel 2 Decimal Places without Rounding (4 Efficient Ways)
- How to Use Phone Number Format in Excel (8 Examples)
- Keep 0 Before a Phone Number in Excel (6 Methods)
- Custom Number Format: Millions with One Decimal in Excel (6 Ways)
3. Utilize CONCATENATE Function to Custom Cell Format Number with Text in Excel
The CONCATENATE function is used to join two cells or text. Follow the instructions below.
Step 1: Add the Number
- After entering the CONCATENATE function, select the numbered cell as text1 with the following formula.
=CONCATENATE(B5)
Step 2: Add Space Between Two Texts
- To separate the two texts, add space (“ ”) between them as text2.
=CONCATENATE(B5," ",)
Step 3: Add Another Text in the End
- Type any text (“USD”) as text3 with the following formula.
=CONCATENATE(B5," ","USD")
Step 4: Get the Result
- Finally, press Enter to obtain the result.
Step 5: Copy the Formula to the Blank Cells
- Use the AutoFill to fill all blank.
Read More: How to Use Number Format Code in Excel (13 Ways)
4. Apply TEXT Function to Custom Cell Format Number with Text in Excel
With the help of The TEXT function, you can do this simultaneously. Firstly, you can convert the number to any desired format. Then, you can add any text to them. Follow the procedures below.
Step 1: Select the Value Argument
- Select any cell with a number by using the following formula.
=TEXT(B5)
Step 2: Insert the format_text Argument
- To apply any format, type any formatting code in the format_text
=TEXT(B5,"#,##0")
Step 3: Add Text
- To add text with the format cell, type any text (“USD”) with the following formula.
=TEXT(B5,"#,##0") & "USD"
Step 4: Final Result
- Therefore, press Enter.
Step 5: Apply the Formula to the All-Blank Cells
- Finally, use AutoFill to fill all the required cells.
Read More: How to Format Number with VBA in Excel (3 Methods)
Conclusion
Finally, I hope you now understand how to custom a cell format number with text in Excel. All of these strategies should be carried out when your data is being educated and practiced. Examine the practice book and apply what you’ve learned. We are driven to continue offering programs like this because of your generous support.
If you have any questions, please do not hesitate to contact us. Please share your thoughts in the comments section below.
The Exceldemy staff will get back to you as soon as possible.
Stay with us and continue to learn.
