There may be instances where you need to add the same text to all cells in a column. You might need to add a particular title before names in a list, or a particular symbol at the end of the text in every cell. The good thing is you don’t need to do this manually. Excel provides some really simple ways in which you can add text to the
beginning and/ or end of the text in a range of cells. In this tutorial we will see 4 ways to do this: So let’s get started! Method 1: Using the ampersand OperatorAn ampersand (&) can be used to easily combine text strings in Excel. Let’s see how you use it to add text at the beginning or end or both in Excel. Using the ampersand Operator to Add Text to the Beginning of all CellsThe ampersand (&) is an operator that is mainly used to join several text strings into one. Here’s how you can use it to add text to the beginning of all cells in a range. Let us assume you have the following list of names and want to add the title “Prof.” before every name: Below are the steps to add a text before a text string in Excel:
That’s it, all your cells in column B should now contain the title “Prof.” preceding each name. Using the ampersand Operator to Add Text to the End of all CellsNow let us see how to add some text to the end of every name in the dataset. Let us say you want to add the text “(MD)” at the end of every name. In that case, here are the steps you need to follow:
All your cells in column C should now contain the text “(MD”) at the end of each name. Method 2: Using the CONCATENATE FunctionCONCATENATE is an Excel function that you can use to add text at the beginning and end of the text string. Let’s see how to use CONCATENATE to do this. Using CONCATENATE to Add Text to the Beginning of all CellsThe CONCATENATE() function provides the same functionality as the ampersand (&) operator. The only difference is in the way both are used. The general syntax for the CONCATENATE function is: =CONCATENATE(text1, [text2], …) Where text1, text2, etc. are substrings that you want to combine together. Let’s apply the CONCATENATE function to the same dataset as above:
That’s it, all your cells in column B should now contain the title “Prof.” preceding each name. Using CONCATENATE to Add Text to the End of all CellsNow let us see how to add some text to the end of every name in the dataset. Let us say you want to add the text “(MD)” at the end of every name. In that case, here are the steps you need to follow:
All your cells in column C should now contain the text “(MD”) at the end of each name. Notice that since you’re using a formula, your column C depends on columns A and B. So if you make any changes to the original values in column A, they get reflected in column C. If you decide to only retain the converted names and delete columns A and B, you will get an error, as shown below: To make sure that this does not happen, it’s best to first convert the formula results to permanent values copying them and pasting them as values in the same column (Right-click and select Paste Options->Values from the Popup menu). Now you can go ahead and delete columns A and B if you need to. Method 3: Using the Flash Fill FeatureFlash fill is a relatively new feature that looks at the pattern of what you are trying to achieve and then does it for all the cells in a column. You can also use Flash fill to so text manipulation as we will see in the following examples. Using Flash Fill to Add Text to the Beginning of all CellsThe Excel flash fill feature is like a magical button. It is available if you’re on any Excel version from 2013 onwards. The feature takes advantage of Excel’s pattern recognition capabilities. It basically recognizes a pattern in your data and automatically fills in the other cells of the column with the same pattern for you. Here’s how you can use Flash Fill to add text to the beginning of all cells in a column:
This will copy the same pattern to the rest of the cells in the column… in a flash! Using Flash Fill to Add Text to the End of all Cells in a ColumnIf you want to add the text “ (MD)” to the end of the names, follow the same steps:
That’s all, you get every cell filled in with the same pattern! We especially like this method because it is simple, quick, and easy. Moreover, since it’s formula-free, the results do not depend on the original columns. So they remain unchanged even if you delete rows A and B! Method 4: Using VBA CodeAnd of course, if you’re comfortable with VBA, you can also add text before or after a text string using it. Using VBA to Add Text to the Beginning of all Cells in a ColumnIf coding with VBA does not intimidate you then this method can help get your work done quickly too. Here’s the code we will be using to add the title “Prof. “ to the beginning of all cells in a range. You can select and copy it: Sub add_text_to_beginning() Dim rng As Range Dim cell As Range Set rng = Application.Selection For Each cell In rng cell.Offset(0, 1).Value = "Prof. " & cell.Value Next cell End Sub Follow these steps to use the above code:
You will now see the converted text next to your selected range of cells. Note: You can change the text in line 6 from “Prof. ” to whatever text you need to add to the beginning of all cells. Using VBA to Add Text to the End of all Cells in a ColumnNow, what if you want to add text to the end of all the cells, instead of the beginning? This only involves making a tweak to line 6 of the above code. So if you want to add the text “ (MD)” to the end of all cells, change line 6 to: cell.Offset(0, 1).Value = cell.Value & “ (MD)” So your full code should now be: Sub add_text_to_end() Dim rng As Range Dim cell As Range Set rng = Application.Selection For Each cell In rng cell.Offset(0, 1).Value = cell.Value & " (MD)" Next cell End Sub Here’s the final result: You can now delete the first two columns if you need to. Do remember to keep a backup of your sheet, because the results of VBA code are usually irreversible. Note: You can change the text in line 6 from “ (MD)” to whatever text you need to add to the end of all cells in the range. In this tutorial, we showed you four ways in which you can add text to the beginning and/ or end of all cells in a range. There are plenty of other methods that you can find online too, and all of them work just as well as the ones shown here. You may feel free to choose whatever method suits you, your requirement, and your version of Excel. In the end, what matters is getting what you need to be done quickly and effectively. Other Excel tutorials you may like:
How do I add text after numbers in Excel?Use a number format to display text before or after a number in a cell. Select the cells that you want to format.. On the Home tab, in the Number group, click the arrow .. In the Category list, click a category such as Custom, and then click a built-in format that resembles the one that you want.. How do I auto populate text in Excel based on another cell?Anyone who has used Excel for some time knows how to use the autofill feature to autofill an Excel cell based on another. You simply click and hold your mouse in the lower right corner of the cell, and drag it down to apply the formula in that cell to every cell beneath it (similar to copying formulas in Excel).
How do I set up Auto Add in Excel?Select a cell next to the numbers you want to sum, click AutoSum on the Home tab, press Enter, and you're done. When you click AutoSum, Excel automatically enters a formula (that uses the SUM function) to sum the numbers.
How do I add predictive text in Excel?On the Excel menu, click Preferences. Under Formulas and Lists, click AutoComplete. In the Show the menu only after I've typed box, enter the number of letters that you want to type before the AutoComplete menu appears. Tip: You can also show the AutoComplete menu by pressing CONTROL + OPTION + DOWN ARROW .
|