When handling Excel datasets of financial management, banking sector, and sales department it is required to add Text Prefixes. However, 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.
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.
How to Add Text Prefix with Custom Format in Excel: 4 Practical Examples
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.
- 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.
- Now, select C5 to C14. And Copy the data by pressing Ctrl + C.
- After copying the data, select D5 and right-click on the mouse. And Paste the copied data as Values.
- Finally, the Pretext will be added to the name and it will look like the below.
Read More: How to Add Prefix Without Formula in Excel
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.
- 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.
- Then immediately, select C5 to C14. And Copy the data by pressing Ctrl+C. After copying the data, select D5 and 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.
3. Insert Metric System Text Prefix with Custom Format
Adding Text Prefixes 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.
- 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.
- Now, select C5 to C14. And Copy the data by pressing Ctrl+C.
- Later, select D5 and 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.
4. Adding Text Prefix with Custom Format Using Excel VBA
VBA allows users to solve repetitive problems by creating user-defined functions. In the following method, we will be using VBA to add Text Prefixes. 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
- Then click Insert and a drop-down menu will appear from there click Module.
- 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.
- Finally, the result will look like the below.
Read More: How to Add Prefix to Entire Column in Excel
How to Add Suffix with Custom Format in Excel
Adding Suffix with a Custom Format 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
00"Kg"
- Hence, press OK.
- After that, select C5 to C14. And Copy the data by pressing Ctrl+C. After copying the data, select D5 and 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.
Read More: How to Add Suffix Without Formula in Excel
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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.
