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

If you are looking for some of the easiest ways to add text after number with custom format in Excel, then you are in the right place. So, let’s start our main article to know more about the procedures of adding texts after numbers.

Download Workbook

4 Ways to Add Text after Number with Custom Format in Excel

Here, we have the following dataset containing the Order Time, Weight, Sales, Profit/Loss of the products of a company. We will try to add texts after these numbers of various columns with a custom format in this article.

We have used Microsoft Excel 365 version here, you can use any other versions according to your convenience.

Method-1: Add Text after Number with Custom Format in Excel

Here, we will add the unit of the weights as Pound after the numeric values of the Weight column using a custom format so that after adding the texts also the numeric values remain in the numeric form. So, later we can try different types of mathematical operations on them.

Steps:
➤ Select the cells of the Weight column.
➤ Go to the Home Tab  >> Number Group >> Number Format dialog box.

Then, the Format Cells wizard will open up.
➤ Go to the Number Tab >> Custom Option >> write the format 00 “Pound” in the Type box.
➤ Press OK.

Afterward, you will get the unit Pound followed by the numeric weight values of the products.

Read More: How to Format Text in Excel Cell (10 Approaches)

Method-2: Add Text after Number with Custom Format for Hours

In this section, we will change the style of the values of the Order Time column by adding AM/PM, and the text string EST after the time format.

Steps:
➤ Select the cells of the Order Time column.
➤ Press CTRL+1 to open up the Number Format dialog box.

After that, the Format Cells wizard will open up.
➤ Go to the Number Tab >> Custom Option >> select the format hh: mm AM/PM from the list of the Types.

Now, you have to add your desired text string after the selected time format.
➤ Type the text string “EST” followed by the time format hh: mm AM/PM.
➤ Press OK.

Finally, you will get your desired time format with a text EST after the time values and you can do any kind of calculations with these times obviously.

Read More: How to Convert Text to Time Format with AM/PM in Excel (3 Methods)

Similar Readings

• How to Change Font Size of the Whole Sheet with Excel VBA
• Excel VBA Codes to Bold Text in a String (5 Examples)
• How to Write 001 in Excel (11 Effective Methods)
• Text Alignment with VBA Command Button in Excel (5 Methods)
• How to Capitalize First Letter of Each Word in Excel (4 Ways)

Method-3: Add Texts Depending on Positive or Negative Number

Here, we have some positive values implying the profits and some negative values indicating the losses of the products in the Profit/Loss column. Our task is here to add the text string Profit or Loss depending on the sign before the values of this column using a custom format.

Steps:
➤ Select the cells of the Profit/Loss column.
➤ Go to the Home Tab >> Number Group >> Number Format dialog box.

Afterward, the Format Cells dialog box will appear.
➤ Go to the Number Tab >> Custom Option >> write the format $0.00 “Profit”;$-0.00 “Loss”  in the Type box.
➤ Press OK.

Finally, we are getting our desired format in the Profit/Loss column which will remain in numeric form so you can add them up to calculate the final Profit/Loss or perform any other calculations.

Read More: How to Add Leading Zeros in Excel Text Format (10 Ways)

Method-4: Add Texts after Number Using Pivot Table

Here, we will use the Pivot Table option to add our desired unit Pound after the numeric weights of the Weight column easily.

Steps:
➤ Go to the Insert Tab >> PivotTable Option.

Then, the PivotTable from table or range dialog box will pop up.
➤ Select the range of your table, click on the New Worksheet option.
➤ Finally, press OK.

Then, you will be taken to a new sheet where you will have two portions; PivotTable1 on the left side and PivotTable Fields on the right side.

➤ Drag down the Product and Weight fields to the Rows area and Values area successively.

➤ To add our custom format to the weights select the Value Field Settings option after clicking the dropdown symbol beside the Sum of Weight.

Then, the Value Field Settings wizard will open up.
➤ Select the Number Format Option.

Afterward, the Format Cells dialog box will appear.
➤ Go to the Custom Option >> write the format 0 “Pound”  in the Type box.
➤ Press OK.

Again, click on OK in the Value Field Settings dialog box.

Eventually, you will get the combination of the numeric values with the text string Pound and here, we can see the total weight also which means they are in the numeric format here.

Using Different Functions to Add Text after Number in Excel

The methods stated before in this article will join the numbers and texts with a custom format so that after adding them they remain in the numeric form and for this reason, we can perform any type of calculations with them.
But, here we will try to join the numeric values and text strings using some functions. After their addition, they will convert into texts so any type of calculation is not possible here.

Let’s start with the CONCATENATE function to add the unit Pound with the numeric values of the Weight column.

➤ Use the following formula to add the numbers with the texts

Here, C4 is the weight value, and CONCATENATE will combine it with the text string “Pound” with a separator as a blank.

You can use the following formula also for joining numbers and texts

Here, C4 is the weight value and TEXTJOIN will combine it with the text string “Pound” with a separator as a blank.

Ampersand operator will perform the same operation as the previous two functions like below.

Here, C4 is the weight value, & will join the weight value with the text string Pound including a blank.

For showing the numeric value in a format and depending on the positive or negative sign if you want to have two different texts like Profit or Loss. Then you can use the IF function and the TEXT function.

➤ Enter the following formula to add the numbers with the texts

Here, C4 is the weight value.

Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

Conclusion

In this article, we tried to cover the ways to add text after number with custom format in Excel. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.

Related Articles

• How to Format Text to Capitalize First Letter in Excel (10 Ways)
• [Fixed!] Unable to Change Font Color in Excel (3 Solutions)
• How to Change Lowercase to Uppercase in Excel (6 Methods)
• How to Use VBA Code to Change Font Color in Excel (3 Methods)
• Capitalize the First Letter in Excel (3 Methods)
• How to Capitalize Each Word in Excel (7 Ways)