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

Get FREE Advanced Excel Exercises with Solutions!

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 learn more about the procedures of adding texts after numbers.


How to Add Text after Number with Custom Format in Excel: 4 Easy Methods

Here, we have the following dataset containing the Order Time, Weight, Sales, and 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 version at 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 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 Add Number with Text in Excel Cell with a Custom Format


Method-2: Add Text after the 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 Format a Number in Thousands K and Millions M in Excel


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 Format Numbers to Millions in Excel


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, and 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 a 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 the TEXTJOIN function 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 to 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


Download Workbook


Conclusion

In this article, we tried to cover the ways to add text after number in 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


<< 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.
Tanjima Hossain
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

4 Comments
  1. hi tanjima i need use custom format similar : number/text/number but after writing system change format similar below

    1400/ب/2502

    how to creat format number/text/number
    thx

    • Hello Ali,
      Hope you are doing well. I tried to create a custom format with number/text/number for numeric values like 14002502.
      • After opening up the Format Cells dialog box, type the following format in the Type area under the Custom tab.
      #### "/Pound/" ####
      Here, #### represents four digits before and after the text. Here, I used “/Pound/” as the text part within inverted commas.

      1

      • After pressing OK, you will get the following results.

      2

      Best Regards
      ExcelDemy

  2. Thank you so much. I have been trying to add text to my numbers and still able to perform calculations. I tried using the concatenation formula but it didn’t but with this article I am able to do what I want

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo