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.

Excel custom format add text after number

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.

Excel custom format add text after number

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

add text after number

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.

add text after number

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

Excel custom format add text after number

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.

Excel custom format add text after number

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

add text after time

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.

add text after time

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.

add text after time

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.

Excel custom format add text after number

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


Similar Readings


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.

Excel custom format add text after number

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

add texts depending on positive and negative signs

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.

add texts depending on positive and negative signs

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.

Excel custom format add text after number

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.

Excel custom format add text after number

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

pivot table

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.

pivot table

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.

Excel custom format add text after number

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

pivot table

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

pivot table

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

Excel custom format add text after number

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

pivot table

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

pivot table

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.

Excel custom format add text after number


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.

Excel custom format add text after number

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

=CONCATENATE(C4," ","Pound")

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

CONCATENATE FUNCTION

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

=TEXTJOIN(" ",TRUE,C4,"Pound")

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

TEXTJOIN FUNCTION

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

=C4&" "&"Pound"

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

Ampersand operator

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.

TEXT FUNCTION

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

=IF(C4>0,TEXT(C4,"$0.00")&" "&"Profit",TEXT(C4,"$0.00")&" "&"Loss")

Here, C4 is the weight value.

  • TEXT(C4,”$0.00″) → gives the format to the value in cell C4
    Output → $100.00
  • TEXT(C4,”$0.00″)&” “&”Profit” becomes
    $100.00 &” “&”Profit” → & operator will join the amount with the text string Profit including a blank
    Output → $100.00 Profit
  • TEXT(C4,”$0.00″)&” “&”Loss” becomes
    $100.00 &” “&”Loss” → & operator will join the amount with the text string Profit including a blank
    Output → $100.00 Loss
  • IF(C4>0,TEXT(C4,”$0.00″)&” “&”Profit”,TEXT(C4,”$0.00″)&” “&”Loss”) becomes
    IF(TRUE, $100.00 Profit, $100.00 Loss) → returns $100.00 Profit for TRUE and $100.00 Loss for FALSE
    Output → $100.00 Profit

TEXT FUNCTION


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.

practice


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

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo