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
- To start with, select a blank cell in another column, such as Cell C1.
- Then, in the selected cell, type the formula in the pattern of = “Prefix ” & Cell, such as the following instance.
= "DataNumen " & A1
- Finally, copy the formula down to add the prefix to the cells that you want.
Method 2: Use “CONCATENATE” Function
- First off, select an empty cell in another column, like Cell C1.
- Then, input the function in pattern of = CONCATENATE(“Prefix “, Cell) in the Cell C1, such as the following one.
= CONCATENATE("DataNumen ", A1)
- Eventually, drag the current cell down to the cells till the last range where you need to add the prefix.
Method 3: Use Excel VBA Code
- In the first place, select the cells which you want to add prefix to.
- Next, trigger Excel VBA editor as per “How to Run VBA Code in Your Excel“.
- 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
- Later, press “F5” to run this macro right now.
- Subsequently, in the small dialog box, enter the specific prefix and click “OK”.
- 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.
|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.
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
Thanks for sharing this code in VBA.
May I suggest as in Excel you don’t need to reference Excel, so code could be reduced to read as follows. Also amended InputBox code to add Title.
Dim objSelectedRange As Range, objRange As Range
Dim strPrefix As String
Set objSelectedRange = Selection
strPrefix = InputBox(“Enter the specific prefix to be added:”, “Add a Prefix to the cell(s) selected”, “Type prefix here…”)
If strPrefix “” Then
For Each objRange In objSelectedRange
objRange.Value = strPrefix & objRange.Value
If adding prefixes is common across several files suggest code is added to it’s own named module in Personal.xlsb and in addition linked to icon on Quick Access Toolbar. If added to QAT, place the associated icon as one of the first nine icons and then can use e.g. Alt + 1 or Alt + 2 etc to run this or click on icon itself.
Hope you find the above of interest and assistance.
MS Excel Consultant Trainer