If a guest list containing 20,000 Professors was handed over to you with an instruction to add the title ‘Prof’ before their names and their occupation ‘Engr’ after their names, how will you carry out this assignment?
Do you belong to the class of persons who will manually input the ‘Prof’ and ‘Engr’ row by row?
Adding text to the beginning or/and end of a range of cells value in excel is a very common task.
Unfortunately, many are unaware that they do not necessarily have to manually carry out this action.
Excel provides some easy methods in which you can add text to the beginning and/ or end of the text in a range of cells, and in this tutorial, we will learn how this is done seamlessly without hassle. Our tutorial focuses on using four (4) methods to achieve.
These are:
- The Flash Fill feature
- The Ampersand Operator
- The CONCATENATE function
- Using VBA
Before we proceed, let us define some terminologies which you will come across in the course of this tutorial.
- Range: A collection of selected cells with an upper reference (the first selected cell) and a lower reference (the last selected cell).
- Function: This is a pre-established formula that performs calculations by using specific values, called arguments, in a particular order E.g., CONCATENATE, AVERAGE, COUNT, COUNTIF
- Condition: This is an expression that evaluates if a statement is TRUE or FALSE
- VBA – This is an acronym for Visual Basic Application. It is an event-driven programming language
- Sub: This is short for Subroutine. A subroutine is primarily a piece of code that executes a specific task described in the code
- Dim: This is an acronym for Declare in Memory. It is used to declare a variable to a specific data type
- Set – This is a statement that is used to assign a value to an object
For this tutorial, we will be using the table shown below to understand how to carry out this task.
This table shows the names of 7 Engineers, who also happen to all be professors of Engineering.
We will be adding the title ‘Prof’ before their names and ‘Engr’ after their names.
| S/N | Name | Guest List |
| 1 | Ekas Osubor | |
| 2 | Chinedu Enebeli | |
| 3 | Nathan Olisedumbiri | |
| 4 | Aki Antonia | |
| 5 | Nduka Ogo | |
| 6 | Henry Joe | |
| 7 | Ene Odioko |
Let’s dive into action!
Here’s How to Add Text to the Beginning or End of all Cells in Excel:
Using the Flash Fill Feature
I love to refer to this feature as a magical tool. The flash fill feature is only present in Excel 2013 and later versions.
Its operation is such that it automatically detects a pattern in your data and replicates it across the cells in a selected range.
To use this feature, you must first confirm that your version of excel is not earlier than 2013 and secondly ensure that it has been activated in your Excel setup.
To turn on the ‘Flash fill’, simply follow these steps:
- Click on the File tab then select Options.
- An ‘Excel Options’ box appears
- Click on the Advanced option
- Navigate to the Editing Options and check the ‘Enable Autocomplete for Cell Value’ and ‘Automatically Flash Fill’ checkboxes to turn on Flash Fill.
Great! So, let’s see the steps on how to use the Flash fill.
Step 1: Click on the cell of the column where you want the names to appear. In our example, this is C2.
Step 2: Manually type in the desired output text. For our example, this will be Prof. Ekas Osubor (Engr)
Step 3: Click on the next cell along the same column (C4)
Step 4: Manually type in the desired output text. For our example, this will be Prof. Chinedu Enebeli (Engr).
Note: Always ensure that the pattern is the same. You will notice that a preview appears as you type in the cell even before you complete the text.
Step 5: If a preview does not appear while typing, simply select the range where you want the result displayed (in our example this range is C2:C8).
Step 6: Click on the Data tab
Step 7: In the Data Tools section, click on ‘Flash Fill’ or r press CTRL + E (COMMAND + E for Mac)
Step 8: This automatically populates the selected range with the output.
Using Flash Fill is simple, quick, and easy.
Even when the other columns are deleted, the output result does not change because it’s formula-free, has no reference, and does not depend on the original columns.
Using the Ampersand (&) Operator
The ampersand (&) operator is used to combine text strings without introducing a function in excel.
Now let us see the steps involved.
Firstly, we will add the text before the string (Prof.), then we add the text after the string (Engr.)
A. Adding the Text before the string
Below are the steps to add a text before a string in Excel using the Ampersand operator:
Step 1: Click the cell where you wish to display your result (In our example Cell C2)
Step 2: Type in the equal to sign (=)
Step 3: Open a quotation mark and type in the text before the string, then close the quotation mark (“Prof.”)
Note: When inputting a condition/formula that has a text within excel, always use a Double Quotation mark. Excel uses the text embedded and discards the quotes while running.
Step 4: Input an ampersand symbol (&)
Step 5: Select the cell where the initial string is located (In our example this is B2)
Step 6: Press the ENTER key on the keyboard
The Cell is automatically updated to capture the First text (Prof) and the string.
Step 7: Select Cell C2. You will notice a square at the bottom right (i.e. the fill handle)
Step 8: Drag down the square box to the last record.
You will notice that all the cells in the selected range along column C contain the title Prof. preceding each name
B. Adding the Text after the string
TO add the ‘Engr.’ Text at the end of every name in the dataset, we will simply follow the steps outlined below:
Step 1: Click the cell where you wish to display your result (In our example Cell D2)
Step 2: Type in the equal to sign (=)
Step 3: Select the cell where the new string is located (In our example this is C2)
Step 4: Input an ampersand symbol (&)
Step 5: Open a quotation mark and type in the text which you wish to appear after the string, then close the quotation mark (“ Engr”)
Step 6: Press the ENTER key on the keyboard
Step 7: Select the cell where the new string is located (In our example this is D2)
The Cell is automatically updated to capture the string and the last text (Engr.)
Step 8: Select Cell D2. You will notice a square at the bottom right (i.e. the fill handle)
Step 9: Drag down the square box to the last record.
You will notice that all the cells in the selected range along column D contain the text ‘(Engr) at the end of each name.
NOTE:
- You can also perform the Ampersand operation at once to add the text before the string and after the string. This will require that the two statements be combined. In our example, if we wish to carry out this operation at a goal, we will input =”Prof “&B2&” (Engr)” in the desired cell and drag along our selected range.
- In this method (the Ampersand operator method), if any of the reference columns is deleted, all columns linked to it will display a Reference error. For example, if we delete column B, the initial results displayed in Column C and D will change to #REF (which depicts a reference error)
To prevent these reference errors, we need to convert the formula result to text values so that they do not reference any column.
To do this Select the desired result output, then right-click and select the copy option.
Paste the copied cell on another column as a Value
Using the Concatenate Function
Concatenate is a function in excel that is used to join texts from different cells together.
It is very similar in functionality to the Ampersand operator.
However, the major difference is in its syntax. The syntax for the CONCATENATE function is:
CONCATENATE (text1, [text2], …)
The text 1 and text2 represent substrings.
A. Adding the Text before the string
To add a text before the name string in our example, simply follow the steps below:
Step 1: Click the cell where you wish to display your converted name/result (In our example Cell C2)
Step 2: Type in the equal to sign (=)
Step 3: Input the function CONCATENATE, then open a bracket.
Step 4: Open a double quote, type in Prof,
Step 5: Close the double quote and enter a comma (,)
Step 6: Select the cell containing the name (B2)
Step 7: Close the bracket. In our example, the formula will be =CONCATENANT(“Prof. “, B2).
Step 8: Press the ENTER key on the keyboard
You will observe at this point, that the title “Prof.” is added before the first name on the list.
Step 9: Select Cell C2. You will notice a square at the bottom right (i.e. the fill handle)
Step 10: Drag down the square box to the last record.
You will notice that all the cells in the selected range along column C contain the title Prof. preceding each name
B. Adding the Text after the string
To add a text after the name string in our example, simply follow the steps below:
Step 1: Click the cell where you wish to display your converted name/result (In our example Cell D2)
Step 2: Type in the equal to sign (=)
Step 3: Input the function CONCATENATE, then open a bracket.
Step 4: Select the cell containing the name with a title (C2), and enter a comma (,)
Step 5: Open a double quote, type in (Engr),
Step 6: Close the double quote
Step 7: Close the bracket. In our example, the formula will be =CONCATENATE(C2,”( Engr)”)
Step 8: Press the ENTER key on the keyboard
You will observe at this point, that the title “(Engr)” is added after the last on the list.
Step 9: Select Cell D2. You will notice a square at the bottom right (i.e., the fill handle)
Step 10: Drag down the square box to the last record.
You will notice that all the cells in the selected range along column C contain the title Prof. preceding each name
NOTE:
- You can also perform the CONCATENATE function at once (i.e., Adding the text before the string and after the string). In our example, if we wish to do this, we will input =CONCATENATE(“Prof. “, B2,” Engr”) in the desired cell and drag along our selected range.
- Just like the Ampersand operator method, if any of the reference columns gets deleted, all cells linked to it will display a Reference error. For example, if we delete column B, the initial results displayed in Column C and D will change to #REF (which depicts a reference error)
To prevent these reference errors, we need to convert the formula result to text values so that they do not reference any column.
To do this Select the desired result output, then right-click and select the copy option.
Paste the copied cell on another column as a Value
Using VBA
VBA allows you to easily automate various activities in Excel.
This is done by writing a program from the backend. I know this sounds like a programmer’s task but trust me when I say it’s very easy.
Firstly, we need to be sure that the “Developer” Tab in your excel ribbon is activated.
If this isn’t the case, simply
- Go to File
- Click on Options from the excel dialog box
- Click on the Customize Ribbon in the left pane
- Check the Developers option on the right pane
- Click OK.
Now let’s get started with VBA!
Here I will show you have to write a program to add text before and after a string.
Step 1: Click on the Developer tab
Step 2: Select the Visual Basic Icon on the Left – the VBA Window opens.
Step 3: Click Insert and Select Module – A blank Module window is created.
Step 4: Copy and paste the code below
Sub add_text_before_and_after_text()
Dim myrng As Range
Dim cell As Range
Set myrng = Application.Selection
For Each cell In myrng
cell.Offset(0, 1).Value = " Prof." & cell.Value & " (Engr)"
Next
End Sub
Let me quickly explain what we are trying to achieve with this code
This is a For Loop program. During the code execution, an analysis of all cells within the range selection is carried out.
For every cell analyzed, a ‘Prof.’text is added before the name, and an ‘(Engr)’ text is added at the end of the name.
Step 5: Close your VBA window
Step 6: Navigate to the worksheet and select the range of cells containing the text you wish to convert (In our example, this is B2:B7)
Note: Ensure that the next column after the selection is blank.
Step 7: Click on the Developer tab
Step 8: Select the Macros Icon on the Left
Step 9: A Micros dialog box pops out
Step 10: In the “Macros name” field, Select the Subroutine name you just created on your VBA (In our example, the subroutine is ‘add_text_before_and_after_text’)
Step 11: Click on the Run button.
You will notice that the converted values occupy the next column.
Note:
- If you wish to perform the Text before string only, simply copy and paste the code shown below
Sub add_text_before_name()
Dim myrng As Range
Dim cell As Range
Set myrng = Application.Selection
For Each cell In myrng
cell.Offset(0, 1).Value = " Prof." & cell.Value
Next
End Sub
- If you wish to perform the Text after string only, simply copy and paste the code shown below
Sub add_text_after_name ()
Dim myrng As Range
Dim cell As Range
Set myrng = Application.Selection
For Each cell In myrng
cell.Offset(0, 1).Value = cell.Value & " (Engr)"
Next
End Sub
Conclusion
In this tutorial, we have learned how to add text to the beginning and/or end of all cells in a range.
Your choice of method to be employed when given a task may depend on the data needs, the version of Excel being used, and your best convenient method.
