How to Add Number with Text in Excel Cell with Custom Format (4 Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

Sample Data


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.

Suitable Ways to Custom Cell Format Number with Text in Excel

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.

Suitable Ways to Custom Cell Format Number with Text in Excel

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”

Suitable Ways to Custom Cell Format Number with Text in Excel

Step 4: Get the Results

  • Finally, press Enter to get the results.

Suitable Ways to Custom Cell Format Number with Text in Excel

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”).

Suitable Ways to Custom Cell Format Number with Text in Excel

Step 2: Get the Result

  • Press Enter to see the first result.

Suitable Ways to Custom Cell Format Number with Text in Excel

Step 3: Copy the Formula to the Blank Cells

  • Therefore, use the AutoFill to fill up the blank cells in the column.

Suitable Ways to Custom Cell Format Number with Text in Excel

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)

Suitable Ways to Custom Cell Format Number with Text in Excel

Step 2: Add Space Between Two Texts

  • To separate the two texts, add space (“ ”) between them as text2.
=CONCATENATE(B5," ",)

Suitable Ways to Custom Cell Format Number with Text in Excel

Step 3: Add Another Text in the End

  • Type any text (“USD”) as text3 with the following formula.
=CONCATENATE(B5," ","USD")

Suitable Ways to Custom Cell Format Number with Text in Excel

Step 4: Get the Result

  • Finally, press Enter to obtain the result.

Suitable Ways to Custom Cell Format Number with Text in Excel

Step 5: Copy the Formula to the Blank Cells

  • Use AutoFill to fill in all blanks.

Suitable Ways to Custom Cell Format Number with Text in Excel

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)

Sample Data

Step 2: Insert the format_text Argument

  • To apply any format, type any formatting code in the format_text
=TEXT(B5,"#,##0")

Sample Data

Step 3: Add Text

  • To add text with the format cell, type any text (“USD”) with the following formula.
=TEXT(B5,"#,##0") & "USD"

Sample Data

Step 4: Final Result

  • Therefore, press Enter.

Sample Data

Step 5: Apply the Formula to the All-Blank Cells

  • Finally, use AutoFill to fill all the required cells.

Sample Data

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


<< Go Back to Custom Number Format | Number Format | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Bhubon Costa
Bhubon Costa

Bhubon Costa, B.Sc. in Naval Architecture & Marine Engineering from Bangladesh University of Engineering & Technology, has worked with the ExcelDemy since 2021. Currently, he has been working as a reviewer. Notably, he has written over 90 articles and led several VBA content development teams. He has a great passion for the fields of data analytics and data science. His areas of expertise include Excel VBA, Power Query, Pivot Table, Power BI, MySQL, PostgreSQL, machine learning, and Python... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo