How To Add Alphabet In Excel Formula

Excel for Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 More...Less

Let's say you need to create a grammatically correct sentence from several columns of data to gear up a mass mailing. Or, mayhap yous need to format numbers with text without affecting formulas that use those numbers. In Excel, in that location are several ways to combine text and numbers.

Utilize a number format to brandish text before or after a number in a cell

If a column that you want to sort contains both numbers and text—such as Product #15, Product #100, Production #200—it may non sort equally yous wait. You can format cells that contain fifteen, 100, and 200 then that they appear in the worksheet as Production #15, Product #100, and Production #200.

Use a custom number format to display the number with text—without irresolute the sorting behavior of the number. In this manner, you alter how the number appears without changing the value.

Follow these steps:

Select the cells that you want to format.
On the Dwelling house tab, in the Number group, click the pointer .
In the Category list, click a category such as Custom, and and so click a built-in format that resembles the one that you desire.
In the Blazon field, edit the number format codes to create the format that you want.

To display both text and numbers in a cell, enclose the text characters in double quotation marks (" "), or precede the numbers with a backslash (\).

NOTE: Editing a congenital-in format does not remove the format.

To display	Employ this code	How it works
12 equally Production #12	"Product # " 0	The text enclosed in the quotation marks (including a space) is displayed before the number in the cell. In the code, "0" represents the number contained in the prison cell (such equally 12).
12:00 as 12:00 AM EST	h:mm AM/PM "EST"	The current time is shown using the date/time format h:mm AM/PM, and the text "EST" is displayed after the time.
-12 as $-12.00 Shortage and 12 every bit $12.00 Surplus	$0.00 "Surplus";$-0.00 "Shortage"	The value is shown using a currency format. In addition, if the cell contains a positive value (or 0), "Surplus" is displayed after the value. If the jail cell contains a negative value, "Shortage" is displayed instead.

Combine text and numbers from different cells into the same cell past using a formula

When you lot do combine numbers and text in a prison cell, the numbers become text and no longer role as numeric values. This means that you can no longer perform any math operations on them.

To combine numbers, use the CONCATENATE or CONCAT, TEXT or TEXTJOIN functions, and the ampersand (&) operator.

Notes:

In Excel 2016, Excel Mobile, and Excel for the web, CONCATENATE has been replaced with the CONCAT function. Although the CONCATENATE function is still available for backward compatibility, you should consider using CONCAT, because CONCATENATE may non be available in future versions of Excel.
TEXTJOIN combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will exist combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges. TEXTJOIN is not available in Excel 2013 and previous versions.

Examples

Come across various examples in the figure below.

Look closely at the use of the TEXT part in the second instance in the figure. When you join a number to a string of text by using the chain operator, use the TEXT function to control the fashion the number is shown. The formula uses the underlying value from the referenced cell (.4 in this example) — not the formatted value you run across in the jail cell (forty%). You use the TEXT office to restore the number formatting.