Mashhura Jahan
Whenever working with Excel you may face a situation where you will need to add comma before text. The main objective of this article is to explain how to add comma before text in Excel. Different quick and easy ways of doing it will be explained.
Download Practice Workbook
You can download the practice workbook from here.
6 Suitable Ways to Add Comma Before Text in Excel
In this article, I will explain 6 suitable methods of adding commas before text in Excel. I have taken the following dataset to explain this article. This dataset contains the Student ID and Name. Suppose you want to add comma before the Name. I will show you how to add comma before text in Excel in easy and quick ways.
1. Use of Flash Fill Command to Add Comma Before Text
In this first method, I will use the Flash Fill command to add comma before text in Excel. I will add a comma before the Names. Let’s see how you can do it.
Steps:
- Firstly, select the first cell of the column in which you want to add comma before text. Here, I selected cell D5.
- Secondly, in cell D5 write the first name with a comma before it.
- Thirdly, press Enter.
- After that, select the cell where you wrote the Name with a comma before it.
- Then, go to the Data tab.
- Next, select Flash Fill.
- Now, you will see that the rest of the cells of the column have been filled automatically in the same format as the first one.
- Click on the Flash Fill Options button.
- Select Accept suggestions.
- Finally, you will see that you have added comma before text and got your desired output.
Read More: How to Add Comma in Excel Between Names (4 Suitable Ways)
2. Insert Comma Before Text Using Ampersand Operator in Excel
The Ampersand (&) operator is used to join 2 or more texts. You can also join texts with formulas by using this operator. Here, I will show you how to add comma before text in Excel by using the Ampersand (&) operator. Let’s see the steps.
Steps:
- First, select the cell where you want to add comma before text. Here, I selected cell D5.
- Next, in cell D5 write the following formula.
=","&C5
- Then, press Enter to get the result.
- After that, drag the Fill Handle down to copy the formula to the other cells.
Here, the Ampersand (&) operator will join these 2 texts together and return a text with a comma before it.
- Finally, in the following picture you can see that I have copied the formula in all the other cells and added comma before the text.
Read More: How to Add Comma in Excel at the End (5 Methods)
3. Apply CONCATENATE Function to Input Comma Before Text
In this method, I will explain how you can apply the CONCATENATE function to add comma before text in Excel. The CONCATENATE function mainly joins two or more text strings into one string. Here, I will use this function to join a comma before the text.
Let’s see the steps.
Steps:
- Firstly, select the cell where you want to add comma before text. Here, I selected cell D5.
- Secondly, in cell D5 write the following formula.
=CONCATENATE(",",C5)
- Thirdly, press Enter and you will get your desired output.
- Next, drag the Fill Handle to copy the formula to the other cells.
Here, in the CONCATENATE function, I selected “,” as text1, and C5 as text2. Now, the CONCATENATE function will join these two texts and return a text with a comma before it.
- Finally, you can see that I have copied the formula to the other cells and added comma before text.
Read More: How to Add Comma in Excel to Concatenate Cells (5 Easy Ways)
4. Include Comma Before Text Using Excel REPLACE Function
The REPLACE function generally replaces specified characters with a specified new text. Here, I will show you how to use the REPLACE function to add comma before text in Excel. I will replace 0 characters with a comma as I don’t want to replace any character from the text and the start number will be 1 as I want the comma at the beginning.
Let me show you the steps.
Steps:
- First, select the cell where you want to add comma before text. Here, I selected cell D5.
- Then, in cell D5 write the following formula.
=REPLACE(C5,1,0,",")
- After that, press Enter to get the result.
- Next, drag the Fill Handle to copy the formula to the other cells.
Here, in the REPLACE function, I selected cell C5 as old_text. Then, I selected 1 as start_num as I want a comma at the beginning of the text. After that, I selected 0 as num_chars as I don’t want to remove any character. And then I selected “,” as new_text. Now, the formula will add a comma before the text in cell C5 and return it as result.
- Finally, in the following image, you can see that I have copied the formula to the other cells and got my desired output.
Read More: How to Add a Comma Between City and State in Excel (6 Ways)
Similar Readings
- How to Change Comma in Excel to Indian style (with Easy Steps)
- [Fixed!] Style Comma Not Found in Excel (2 Possible Solutions)
- How to Convert Column to Row with Comma in Excel (3 Ways)
- Change Comma Style in Excel (4 Quick Ways)
- How to Change Semicolon to Comma in Excel (6 Effective Ways)
5. Use SUBSTITUTE Function to Get Comma Before Text
In this example, I will use the SUBSTITUTE function to add comma before text in Excel. For this example, I have taken the following dataset. It contains the Student ID-Name column. This column contains both numbers and text and they are separated by a Hyphen (-). I will show you how you can remove the Hyphen (-) and get a comma before the text.
Let’s see the steps.
Steps:
- Firstly, select the cell where you want to add comma before text. Here, I selected cell C5.
- Secondly, in cell C5 write the following formula.
=SUBSTITUTE(B5,"-",",")
- Thirdly, press Enter to get the result.
- After that, drag the Fill Handle to copy the formula to the other cells.
Here, in the SUBSTITUTE function, I selected B5 as text, “-“ as old_text, and “,” as new_text. Now, the formula will replace the “-“ with a “,” in the text in cell B5 and return it as result.
- Finally, in the following picture, you can see that I have copied the formula to the other cells and got my desired result.
Read More: How to Put Comma in Numbers in Excel (7 Easy Ways)
6. Employ VBA to Add Comma Before Text in Excel
VBA is a computer programming language that is developed and owned by Microsoft. In this method, I will show you how you can employ VBA to add comma before text in Excel. I will write a VBA code so that you can add commas before text by just running the code. Let’s see the steps.
Steps:
- Firstly, go to the Developer tab.
- Secondly, select Visual Basic.
- Now, the Visual Basic Editor window will open. You can also press Alt + F11 from your keyboard to open the Visual Basic Editor.
- After that, select the Insert tab.
- Then, select Module.
- Here, you will see a Module will open.
- Now, write the following code in the Module.
Sub adding_comma_before_text()
Dim row_no As Integer
For row_no = 5 To 12
Worksheets("Employing VBA").Cells(row_no, 4).Value = "," & Worksheets("Employing VBA").Cells(row_no, 3).Value
Next row_no
End Sub
🔎 How Does the Code Work?
- To begin with, I created a Sub Procedure named adding_comma_before_text.
- Then, I declared a variable named row_no as Integer.
- Next, I use a For Next Loop to run the code through rows 5 to 12.
- After that, I used the Ampersand (&) operator to add comma before the texts in column C and return the value in column D.
- Finally, I ended the Sub Procedure.
- Further, Save the code and go back to your worksheet.
- Again, go to the Developer tab.
- Select Macros.
- Consequently, the Macro dialog box will appear.
- From here, select adding_comma_before_text as the Macro name.
- In the end, select Run.
- Finally, you will see that you have added comma before text as you wanted using Macros.
Read More: How to Insert Comma Between Words in Excel (4 Simple Methods)
How to Add Comma Before First Text Character in Excel
In this section, I will show you how you can add comma before the first text character in Excel. Suppose you have a string that contains both numeric characters and text characters. I will show you how you can add a comma before the first text character in the string. To explain this example I have taken the following dataset. It contains the Product Name and Product Code. I will add a comma before the first text character in the Product Code.
Let’s see how it is done.
Steps:
- First, select the cell where you want to add a comma before the first text character. Here, I selected cell D5.
- Next, in cell D5 write the following formula.
=REPLACE(C5,MATCH(TRUE,ISERROR(VALUE(MID(C5,ROW(INDIRECT("1:"&LEN(C5))),1))),0),0,",")
- Now, press Enter and you will get the result.
- Then, drag the Fill Handle to copy the formula to the other cells.
🔎 How Does the Formula Work?
- LEN(C5): The LEN function will return the number of characters in the text string in cell C5.
- “1:”&LEN(C5): Now, the Ampersand (&) operator will join these texts and formulas.
- INDIRECT(“1:”&LEN(C5)): The INDIRECT function will return the reference specified by “1:”&LEN(C5).
- ROW(INDIRECT(“1:”&LEN(C5))): The ROW function will return the row numbers of the reference.
- MID(C5,ROW(INDIRECT(“1:”&LEN(C5))),1): Here, the MID function will return 1 character from the text string in cell C5 starting with the numbers it got from the ROW function.
- VALUE(MID(C5,ROW(INDIRECT(“1:”&LEN(C5))),1)): Now, the VALUE function will convert the numbers in the text strings to number and return #VALUE! error for the text characters.
- ISERROR(VALUE(MID(C5,ROW(INDIRECT(“1:”&LEN(C5))),1))): The ISERROR function will return TRUE for any error it finds.
- MATCH(TRUE,ISERROR(VALUE(MID(C5,ROW(INDIRECT(“1:”&LEN(C5))),1))),0): Here, the MATCH function will look for an exact match of TRUE and return the relative position of it.
- REPLACE(C5,MATCH(TRUE,ISERROR(VALUE(MID(C5,ROW(INDIRECT(“1:”&LEN(C5))),1))),0),0,”,”): Next, the REPLACE function will add a comma before the first text character as the MATCH function will return the position of the first text character.
- Finally, you can see that I have copied the formula to the other cells and added comma before the first text character.
Read More: How to Insert Comma in Excel for Multiple Rows (3 Suitable Ways)
Things to Remember
- Whenever working with VBA you must save the Excel file as Excel Macro-Enabled Workbook. Otherwise, the VBA code will not work.
Practice Section
Here, I have provided a practice sheet for you to practice how to add comma before text in Excel.
Conclusion
To conclude, I tried to cover how to add comma before text in Excel. Here, I explained 6 different methods. I hope this article was helpful to you. If you have any questions feel free to let me know in the comment section below.
