July 2021 – LearnYouAndMe

In this Excel tutorial, you will learn how to add prefix or suffix to a cell using formula and using VBA.

You will also see adding a prefix to date without changing date format.

Also, VBA to add a prefix to worksheet names in Excel.

Formula to add prefix or suffix to a cell

Adding Prefix

Concatenate function can be used to add a prefix to a text.

To add a prefix, prefix word is supplied before the original text.

In the below image job role needs to be prefixed with the name.

Job role and name should be separated by a dash (” – “).

Cell C2 formula =CONCATENATE(B2,” – “,A2)

Alternatively, you can use & (ampersand) to add a prefix like below

=A2&” – “&B2

Note:- In the latest versions of Excel Cocat and Textjoin functions can be also used.

Adding Suffix

To add a suffix, suffix word is supplied after the original text.

Cell C2 formula =CONCATENATE(A2,B2)

Add prefix to a Date in Excel

To add a prefix to a Date there is one twist.

While concatenating date with a text, date format gets changed and date gets converted into its respective number serial.

Cell B2 formula =CONCATENATE(“Dt.-“,A2)

In the below image you can see after the concatenation date format is changing.

Keeping Date format while concatenating cell

To keep the date format unchanged while concatenating, just wrap the date in the Text function.

Cell B2 formula =CONCATENATE(“Dt.-“,TEXT(A2,”dd-mm-yyyy”))

You can see date cell (A2) is wrapped in Text function.

TEXT(A2,”dd-mm-yyyy”)

Add prefix in Excel without formula using VBA

Here, you will see how to add a prefix to a range or column using VBA.

Prefixing using VBA does not require any formula and additional helper column.

VBA to add same prefix to selected cells

Below is the VBA code to add prefixes to selected cells or range.

You can change variable value prefix = “Delhi-“ to something else as per your requirement.

Just select the cells and run VBA.

Sub addSamePrefix()
''add same prefix to selected cells
  Dim rng As Range, cel As Range, prefix As String
  
  Set rng = Selection
  prefix = "Delhi-"
  
  For Each cel In rng
     cel.Value = prefix & cel.Value
  Next cel
  
  rng.EntireColumn.AutoFit
End Sub

VBA to add different prefix to a range or column

Below is the VBA code to add prefix to a column or range.

You can change the below variables as per your requirement.

Set preFxRng = Sheet1.Range(“B2:B8”)

Set adToRng = Sheet1.Range(“A2:A8”)

Sheet1 is a worksheet code name in which range is present.

Sub addDiffPrefix()
 'add different prefix to a range cells
 Dim preFxRng As Range
 Dim adToRng As Range
 Dim cel As Range, i As Long
 
 Set preFxRng = Sheet1.Range("B2:B8")
 Set adToRng = Sheet1.Range("A2:A8")
 
 i = 1
 For Each cel In adToRng
   cel.Value = preFxRng.Cells(i, 1).Value & "-" & cel.Value
   i = i + 1
 Next cel
 
 adToRng.EntireColumn.AutoFit
End Sub

VBA to add prefix or suffix to sheet tab name

Add same prefix to all the sheets tab name

Sub addPrefixToSheetNm()
  Dim sht As Worksheet
  Dim prefix As String
  
  prefix = "Cate_"
  
  For Each sht In ThisWorkbook.Worksheets
    sht.Name = prefix & sht.Name
  Next sht
End Sub

Add specific prefix to specific sheets tab name

First, you need to get all the sheet names then write their respective prefixes to add.

To get all the sheet names quickly you can use the below VBA code.

Note:- Sheet names will be written in the active sheet, so make sure the active sheet is blank before running the below code.

VBA to get worksheet (sheet) names

Sub getSheetNames()
 'this will write sheet names on ACTIVE sheet
 'make sure no data is there on active sheet
 'before running this VBA
 
 Dim sht As Worksheet, i As Integer
 Set sht = ActiveSheet
   
   sht.Range("A1").Value = "SheetNames"
   sht.Range("B1").Value = "Prefix"
   
 For Each sht In ThisWorkbook.Worksheets
   Range("A2").Offset(i).Value = sht.Name
  i = i + 1
 Next sht
 
End Sub

VBA code to add specific prefix to specific sheet name

In the below code section Sheet1 is the code name of a worksheet in which the sheet names and prefixes are written.

You can the Sheet1 to other sheet code name as per your requirement.

Sub addPrefixToSheetName()
 Dim shtNmRng As Range
 Dim prfxRng As Range
 Dim cel As Range, i As Long
 
 'Sheet1 is code name of the sheet in which sheet name and prefix is written
 Set shtNmRng = Sheet1.Range("A2:A5")
 Set prfxRng = Sheet1.Range("B2:B5")
 
 i = 1
 For Each cel In shtNmRng
   Worksheets(cel.Value).Name = _
   prfxRng.Cells(i, 1).Value & cel.Value
   i = i + 1
 Next cel
End Sub