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.
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.
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 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.
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 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.
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 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.
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, and 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 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.
➤ 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.
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.
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.
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 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
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.
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.
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.
• After pressing OK, you will get the following results.
Best Regards
ExcelDemy