If you frequently work with Excel spreadsheets, you may need to add a comma character at the end of cells for various reasons, such as creating lists or formatting data for import/export.
This post will guide you on how to add a comma at the end of cells in Excel using different methods. One way to achieve this is by using the concatenation operator “&” or the CONCATENATE function. Alternatively, you can use VBA code to automate the process and save time when dealing with large amounts of data.
Table of Contents
1. Adding Comma Character at End of Cells using Concatenation Operator
If you want to add a comma character at end of cells in a range, you just need to use the concatenation operator to create a formula. Like this:
Type this formula in a blank cell, and then press Enter key. And then drag the AutoFill handle over other cells to apply this formula.
You will see that the comma character has been added at the end of all cells.
2. Adding Comma Character at End of Cells Using CONCATENATE Function
You can also add a comma character at the end of cells in excel using CONCATENATE function, you can following these steps:
Step1: Select one blank cell where you want to add the comma.
Step2: type the formula:
Step3: Replace “cell reference” with the reference to the cell that you want to add the comma to. Press “Enter” to complete the formula.
For example, if you want to add a comma at the end of the text in cell B1, the formula would be:
Once you enter this formula, the text in cell A1 will be displayed with a comma at the end.
Step4: You can also use this function to add a comma at the end of multiple cells at once by dragging the formula down to the other cells you want to include.
3. Adding Comma Character at End of Cells with VBA Code
If you want to add a comma character at the end of cells in Excel using VBA code, you can follow these steps:
Step1: Open the Visual Basic Editor by pressing Alt + F11.
Step2: In the VBA Editor, insert a new module by going to “Insert” > “Module“.
Step3: In the module, paste the following code:
Sub AddCommaToEnd_ExcelHow() 'Declare variables Dim sourceRange As Range Dim destRange As Range Dim cell As Range 'Prompt user to select source range Set sourceRange = Application.InputBox("Select source range", Type:=8) 'Loop through each cell in the source range For Each cell In sourceRange 'Add comma to end of cell value cell.Value = cell.Value & "," Next cell End Sub
Step4: run the code by clicking the “Run” button (or pressing F5) or press ALT +F8 to open Macro dialog box. Select Macro name as you need. Clicking on Run button.
Step5: When the code runs, it will prompt the user to select a range of cells to modify.
Step5: The Comma Character would be added at the end of each cell in the selected range.
4. Video: Adding Comma Character at End of Cells
In this video, you will learn how to add a comma character at the end of cells in Excel using different methods such as concatenation operator, CONCATENATE function, and VBA Code.