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 add numbers with text using a custom format in Excel cell.
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. Using Formal Cells Dialog Box to Create Custom Number with Text in Excel
To customize numbers with text by using the Format Cells box, follow 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: How to Apply Custom Number Format in Excel with Multiple Conditions
2. Applying Excel Formula to Create Custom Cell Format Number with Text
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.
Read More: How to Apply Custom Format Cells in Excel
3. Inserting Excel CONCATENATE Function for Custom Cell Format Number with Text
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 AutoFill to fill in all blanks.
Read More: Excel Custom Number Format – Millions with One Decimal
4. Applying 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 Add Text after Number with Custom Format in Excel
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
Finally, I hope you now understand how to customize 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.
Related Articles
- How to Format a Number in Thousands K and Millions M in Excel
- How to Apply Number Format in Millions with Comma in Excel
- How to Format Number to Millions in Excel
<< Go Back to Custom Number Format | Number Format | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!