How to add a letter in front of a number in Microsoft Excel

Adding a letter in front of a number is a simple task that can be important in certain contexts where the distinction between numbers and other types of data is important. They may be used to Identify product or part numbers, Create reference numbers, or Categorize data.

In this tutorial, we will learn how to add a letter in front of a number in Microsoft Excel. In Excel, there are four methods to add a letter in front of a number. These include using the “&” operator, the CONCATENATE function, and a custom number format. Each of these methods involves combining the letter and number into a single text string, either through concatenation or by formatting the number.

At present, we have a collection of 5-digit numbers that comprise the numerical portion of a product code. Our task is to add a letter that signifies the product category to each of these numbers.

Method 1: Using the Custom Number Format

This method is useful when you wish to add the same letter to all the dataset. In our case we’ll add A – in front of the numbers.

Step 1 – Select the Cells

• Select the cells containing the numbers.

Step 2 – Right-Click on the Selected Cells

• Right-click on the selected cells.
• A context menu will appear.

Step 3 – Click on the Format Cells Option

• Click on the Format Cells option in the context menu.

Step 4 – Select the Custom Format

• Select Custom as the format in the Category section.

Step 5 – Enter the Custom Format

• Enter (“A – ” # ?/?) as the custom format, without the parenthesis in the Type field.
• Where “A” is any letter to be added in front of the numbers.

Step 6 – Click on OK

• Click on OK in the Format Cells dialog box.
• The letter will be added in front of the numbers.

Method 2: Using the Ampersand Operator

Step 1 – Select a Blank Cell and Place an Equals Sign

• Select a blank cell where you want to print the numbers after adding a letter.
• Place an Equals sign in the blank cell.

Step 2 – Use the Ampersand Operator

• Use the Ampersand operator to add a letter,
• The syntax of the formulae will be:

                  “A – ” & D2

• Where “A” is any letter to be added.
• D2 is the reference of the cell containing the number.

Step 3 – Press the Enter Key

• Press the Enter key to add the letter.

Step 4 – Use Autofill to Apply the Formula to Each Number

• Use the autofill to apply the formulae on each cell containing the number.

Method 3: Using the CONCATENATE Function

Step 1 – Select a Blank Cell and Place an Equals Sign

• Select a blank cell where you want to print the numbers after adding a letter.
• Place an Equals sign in the blank cell.

Step 2 – Use the CONCATENATE Function

• Use the CONCATENATE function to add the letter.
• The syntax of the CONCATENATE function will be: 

   CONCATENATE(“A – ”, D2)

• Where “A” is any letter to be added and D2 is the cell containing the number.

Step 3 – Press the Enter Key

• Press the Enter key to add the letter.

Step 4 – Use Autofill to Apply the Formula to Each Number

• Use the autofill to apply the formulae on each cell containing the number.