WPS Office

Free All-in-One Office Suite with PDF Editor

correct-icon

Edit Word, Excel, and PPT for FREE.

correct-icon

Read, edit, and convert PDFs with the powerful PDF toolkit.

correct-icon

Microsoft-like interface, easy to use.

Free download

Windows • MacOS • Linux • iOS • Android

banner

How to create custom number format with text in Excel

August 1, 2022
10.3K Views

We'll employ various distinct methods to add text and numbers to a cell format in the sections that follow. We'll start by using the Format Cells box. Formulas, the CONCATENATE function, and the TEXT function will all be used later. To help you comprehend the goal of our job, an example data set is supplied.

In Excel, use the Formal Cells Box to create custom numbers with text:

The steps below explain how to add text to integers using the Format Cells box.

1.First step: Choosing the cells.

Choose every cell.

2.Open the Format Cells box in step two.

Ctrl + 1 will launch the Format Cells window.

On the Custom, click.

Choose whatever format you require or type it.

3.Add text in the Type Box in Step 3

Enter any text enclosed in the () in the Type box. For illustration, we shall add USD at the end using the formula below. #,##0 USD

4.Get the results in Step 4

To retrieve the results, hit Enter at the very end.

Use an Excel Formula to Create a Custom Cell Format Number with Text:

You may format a cell number with text by using the formula operator ampersand (&). Simply carry out the instructions.

Step 1: Add a formula.

Select any cell first.

Put a & in there 

The text marked by the inverted comma (USD) should be typed.

Step 2: Obtain the outcome

To view the very first outcome, press Enter.

Step 3: paste the formula into the empty cells

Use AutoFill to fill up the column's empty cells as a result.

Use Excel's CONCATENATE function to create custom cell formats that include text and numbers:

Two cells or pieces of text can be joined using the CONCATENATE function. The steps are listed below.

First, add the number.

Select the numbered cell as text1 with the formula below after entering the CONCATENATE function.

=CONCATENATE(B5)

Add Space Between Two Texts in Step 2

Add a space ( ) between the two texts as text2 to separate them (B5, ,)

Step 3: Finish the sentence with more text.

Enter any text (USD) as text3 using the formula below.

=CONCATENATE (B5, ,USD)

Step 4: Obtain the outcome

Finally, hit Enter to get the outcome.

Copy the formula to the empty cells in step five.

To fill in all of the blanks, use AutoFill.

Use Excel's TEXT function to format a custom cell with text and numbers:

You may accomplish both of these at once with the aid of the TEXT function. To start, you can format the number in any way you choose. Any text may then be added to them. Observe the steps listed below.

Step 1: choose the value argument.

The formula below may be used to choose any cell that contains a number.

=TEXT(B5)

Step 2:

Insert the format text Argument

Enter any formatting code in the format code   =TEXT(B5,#,##0) field to apply any format.

Step 3: Insert Text

Enter any text (USD) using the formula below to add text to the format cell.

=TEXT(B5,#,##0) & USD

Step 4: Get Result by pressing Enter

Step 5: Fill in all of the blank cells with the formula

Lastly, fill up all the cells using AutoFill.

Did you learn about how to custom cell format number with text in Excel file? You can follow WPS Academy to learn more features of Word Document, Excel Spreadsheets and PowerPoint Slides.

You can also download WPS Office to edit the word documents, excel, PowerPoint for free of cost. Download now! And get an easy and enjoyable working experience.

15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.