How to Add Text Prefix with Custom Format in Excel (4 Examples)

When handling Excel datasets of financial management, banking sector, and sales department it is required to add Text Prefixes. But it happens to be a time-consuming task to add prefixes or suffixes individually. So, this article will cover the process of using Custom Format of MS Excel to add preferable Text Prefix before any kind of data. Here is the overview of adding text prefixes with a custom format.

Insertion of Text Prefix with Custom Format in Excel


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


What Is Text Prefix?

Text Prefix in Excel refers to the data or string of characters used before any data or value in Excel. To exemplify, suppose that the “Dr. Chandler Bing” is data stored in a Cell where the string “Dr.” is considered the Text Prefix.


4 Practical Examples to Add Text Prefix with Custom Format in Excel

1. Add Text Prefix for Designation

While working on the Excel dataset, sometimes you need to add the designation of people. You can easily add a designation before any name using Text Prefix with Custom Format in Excel. From our dataset, we will add “Dr.” before several names as a text prefix. To do so, please follow the steps below.

Steps:

  • In the beginning, select cells from D5 to D14.
  • After selecting the cell, press Ctrl+1.

Selection of column for Custom Format for Text Prefix

  • After that, the Format Cell window appears. From there, go to the Number Then choose the Custom option from the category drop-down list. And then in the Type box, type the below custom formula.
"Dr." @
  • And then press OK.

Addition of formula for Prefix in the Custom tab

  • Now, select C5 to C14. And Copy the data by pressing Ctrl + C.

Selection of data that are going to use Prefix

  • After copying the data, select D5 and make Right Click on the mouse. And Paste the copied data as Values.

Pasting the copied data as Values

  • Finally, the Pretext will be added to the name and it will look like the below.

Outcome of adding Prefix


2. Adding Text Prefix for Currency

While working with currency in Excel, it is required to add a currency sign. But if it seems a tedious process if someone wants to add a sign manually. In that case, adding the Currency sign as Text Prefix using Custom Format is quite efficient.

Steps:

  • First, select cells from D5 to D14.
  • Afterward, from the Home option, press the icon in the downward corner as shown in the picture below.

Going to Custom format option for Text Prefix

  • Consequently, the Format Cell window pops up. From the Format Cell window, go to the Number Then choose the Custom option from the category drop-down list. And then in the Type box, type the formula below
$ 0000
  • Hence, press OK.

Addition of formula

  • Then immediately, select C5 to C14. And Copy the data by pressing Ctrl+C. After copying the data, select D5 and make Right Click on the mouse. And Paste the copied data as Values.
  • In the end, the Pretext will be added to the name and look like the one below.

Result of addition of currency sign as Text Prefix


3. Insert Metric System Text Prefix with Custom Format

Adding Text Prefix to the Metric System with Custom Format is similar to the previous method. We will add mili (m) before some parameters. Follow the steps below.

Steps:

  • To begin with, select cells from D5 to D14.
  • Then make a Right Click at the mouse and a drop-down menu will appear. From there press Format Cell.

Method of accessing Format Cells

  • After that, the Format Cell window appears. From there, go to the Number Then choose the Custom option from the category. And then in the Type formula below.
"m" @
  • Further, press OK.

apply "m" (milli) as text prefix with custom format

  • Now, select C5 to C14. And Copy the data by pressing Ctrl+C.
  • Later, select D5 and make Right Click on the mouse. And Paste the copied data as
  • At last, the Pretext will be added to the name and it will look like the below.

Outcome of adding mili(m) as text prefix


4. Adding Text Prefix with Custom Format Using Excel VBA

VBA (Visual Basic for Application) allows users to solve repetitive problems by creating user-defined functions. In the following method, we will be using VBA to add Text-Prefix. Please follow the steps to execute the task.

Steps:

  • Stay on the worksheet that you want to work on.
  • Then, choose Developer from the menu and click the Visual Basic option as follows

Going to the developer option to execute VBA Code

  • Then click Insert and a drop-down menu will appear from there click Module.

Going to the module section for adding the code

  • Then copy and paste the code as follows,
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
  • Now, press F5 or click Run to execute the code.

Running VBA code for adding Text Prefix

  • Finally, the result will look like the below.

Outcome of the addition of VBA code


How to Add Suffix with Custom Format in Excel

Adding Suffix as Text-Prefix is similar to adding Prefix. Here, we will add “Kg” as a suffix after weights numeric data. Please follow the steps mentioned below.

Steps:

  • To begin the process, select from D5 to D14
  • After that, press Ctrl+1.
  • Subsequently, the Format Cell window pops up. From the Format Cell window, go to the Number Then choose the Custom option from the category drop-down list. And then in the Type box, type the formula below
<span style="font-size: 14pt;"><strong>00"Kg"</strong></span>

Addition of suffix formula

  • Hence, press OK.
  • After then, select C5 to C14. And Copy the data by pressing Ctrl+C. After copying the data, select D5 and make Right Click on the mouse. And Paste the copied data as Values.
  • Lastly, the Pretext will be added to the name and look like the one below.

Outcome of Addition of suffix to the data and texts


Conclusion

This article covered the main idea of adding Text-Prefix with a Custom Format in Excel. Additionally, you can practice this Excel problem by downloading from this page. Yet, if you have any queries related to this problem please comment below. Moreover, you can visit www.exceldemy.com if you want to learn more about Excel-related issues.

Zahid Shuvo

Zahid Shuvo

Hi! Welcome to my profile. I am Zahid Hasan. I have completed my bachelor of science in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology (BUET). With increasing interest in research work, I am looking forward to providing the best possible solution for the excel related problem and article ahead.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo