3 Handy Methods to Batch Add Prefix to Multiple Cells in Excel Worksheet

Excel File Recovery, Excel Solutions February 24, 2022

If you would like to batch add a specific prefix to multiple cells, you can refer to this article. Here we will introduce you 3 easy methods. Each has its own pros and cons. You can choose one as per your needs.

At times, you may need to add a specific text to the beginning of multiple cells in an Excel worksheet. For those Excel newbies, they may tend to add the prefix to each cell one by one. However, it will be extremely time-consuming if there are a great number of cells to be processed. Thereby, you’re better off using any of the following 3 means.

Method 1: Use “&” Formula

  1. To start with, select a blank cell in another column, such as Cell C1.
  2. Then, in the selected cell, type the formula in the pattern of = “Prefix ” & Cell, such as the following instance.
= "DataNumen " & A1

Input "= "Prefix " & Cell" Formula in Cell C1

  1. Finally, copy the formula down to add the prefix to the cells that you want.Copy "&" Formula Down

Method 2: Use “CONCATENATE” Function

  1. First off, select an empty cell in another column, like Cell C1.
  2. Then, input the function in pattern of = CONCATENATE(“Prefix “, Cell) in the Cell C1, such as the following one.
= CONCATENATE("DataNumen ", A1)

Input "= CONCATENATE("Prefix ", Cell)" Formula in Cell C1

  1. Eventually, drag the current cell down to the cells till the last range where you need to add the prefix.Copy "CONCATENATE" Formula Down

Method 3: Use Excel VBA Code

  1. In the first place, select the cells which you want to add prefix to.
  2. Next, trigger Excel VBA editor as per “How to Run VBA Code in Your Excel“.
  3. Then, copy the following code into an unused project or module.
Sub AddPrefixToAllSelectedCells()
    Dim objSelectedRange As Excel.Range
    Dim strPrefix As String
    Dim objRange As Excel.Range
 
    Set objSelectedRange = Excel.Application.Selection
    strPrefix = InputBox("Enter the specific prefix to be added:", , "DataNumen ")
 
    If strPrefix <> "" Then
       For Each objRange In objSelectedRange
           objRange.Value = strPrefix & objRange.Value
       Next
    End If
End Sub

VBA Code - Batch Add a Prefix to Multiple Cells

  1. Later, press “F5” to run this macro right now.
  2. Subsequently, in the small dialog box, enter the specific prefix and click “OK”.Enter Specific Prefix
  3. At once, after macro finishes, you can return to the Excel worksheet to find that the prefix has been added to the selected cells successfully.Batch Added Prefix

Comparison

  Advantages Disadvantages
Method 1 Easy to operate 1. Can’t process cells in one column at once
Method 2 2. Can’t add prefix to the original cells directly
Method 3 1. Can directly add prefix to the original cells VBA novices may be confused
2. Can batch process the cells in different columns

Excel Repair after Crash

Excel is admittedly unstable and tends to crash from time to time. Thereby, in this way, Excel file can also get corrupted readily. Hence, if you don’t want to confront Excel data loss, you need to back up the files on a regular basis. Besides, it is also a matter of necessity to have a robust tool in vicinity, such as DataNumen Excel Repair to repair Excel file.

Author Introduction:

Shirley Zhang is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including SQL Server fix and outlook repair software products. For more information visit www.datanumen.com

Comments are closed.