How to Add a Text Prefix with a Custom Format in Excel – 4 Examples

This is an overview:

Example 1 – Add a Designation Prefix

Steps:

• Select D5:D14.
• Press Ctrl+1.

• In the Format Cells window, go to Number.
• Choose Custom in Category.
• In Type:, enter the formula.

• Click OK.

• Select C5:C14 and Copy the data by pressing Ctrl + C.

• Select D5 and right-click. Paste the copied data as Values.

This is the output.

Read More: How to Add Prefix Without Formula in Excel

Example 2 – Adding a Currency Prefix

Steps:

• Select D5:D14.
• In the Home tab, click the icon as shown below.

• In the Format Cells window, go to Number.
• Choose Custom in Category.
• In Type:, enter the formula.

• Click OK.

• Select C5:C14 and Copy the data by pressing Ctrl + C.
• Select D5 and right-click. Paste the copied data as Values.

This is the output.

Example 3 – Insert a Metric System Text Prefix with a Custom Format

Steps:

• Select D5:D14.
• Right click and select Format Cells.

• Go to Number.
• Choose Custom in Category.
• In Type:, enter the formula.

• Click OK.

• Select C5:C14 and Copy the data by pressing Ctrl + C.
• Select D5 and right-click. Paste the copied data as Values.

This is the output.

Example 4 – Adding a Text Prefix with a Custom Format Using Excel VBA

Steps:

• Open the worksheet.
• Go to the Developer tab and click Visual Basic.

• Select Insert and click Module.

• Copy the code:

Sub SOFTEKOaddPrefix()
Dim i As Integer
Dim n
Dim p
Dim result
For i = 5 To 14
p = Cells(i, 2).Value
n = Cells(i, 3).Value
Cells(i, 4).Value = p & " " & n
result = Cells(i, 4).Value
Next
End Sub

• Press F5 or click Run to run the code.

This is the output.

Read More: How to Add Prefix to Entire Column in Excel

How to Add a Suffix with a Custom Format in Excel

Steps:

• Select D5:D14
• Press Ctrl+1.
• In Format Cells, go to Number.
• Choose Custom in Category.
• In Type:, enter the formula.

• Click OK.
• Select C5:C14 and Copy the data by pressing Ctrl + C.
• Select D5 and right-click. Paste the copied data as Values.

This is the output.

Read More: How to Add Suffix Without Formula in Excel

Download Practice Workbook

Download the practice workbook.

Related Articles

• How to Add Suffix in Excel
• How to Remove Prefix in Excel
• How to Remove Prefix 91 in Excel

<< Go Back to Suffix and Prefix | Text Formatting | Learn Excel