After the first word in a randomly generated list in Excel, commas are often lacking from the data, a recurring issue in the program. Data moved straight from word processors or spreadsheets are especially susceptible to corruption. For more information on How to Put a Comma after the first word in each cell? Please see this tutorial.
When working on an Excel Worksheet, many circumstances might develop where the last name is not separated from the first name by a period. Manually separating the two names can be a time-consuming and complex procedure. Thanks to the information provided in this article, you can easily insert a period (.) between two names in Excel by utilizing several formulae.
There are three methods for doing this task: we may use the replace function, the replacement function, and the left function to insert commas after the first word in each cell, or we can utilize the proper function. These formulae for adding commas will be shown in detail later in this chapter.
Replace Function To Add Commas
This work may be completed quickly and simply by combining the replace function with a formula. Only the formula for this particular piece of work has to be remembered. It is this formula that we will be dealing with: “=REPLACE(A1,FIND(” ‘,A1),0,”,”)
Step 1: Open up a new Excel sheet and enter your data, we have used names, and we will be using this formula to add a comma after the first name.
Step 2: Enter the formula in your preferred cell, the formula “=REPLACE(A1,FIND(” “,A1),0,”,”)”. Here A1 stands for the first cell of the data, the “” marks the first word ai cell A1 0 is the space, and the “,” is the comma placed after the first word. After placing the formula, press enters, and a comma will separate the names.
Step 3: All that is left to do is drag the Autofill handle to the end of the data range and it will separate all the names or data with a comma.
Substitute Function To add Comma
We may utilize the substitution function, a more straightforward formula, but it performs the same job as the original function. In this case, the formula is straightforward: “=SUBSTITUTE(A1,” “,”,”, “,1)” All you have to do is alter the A1 cell range to correspond to the range of your data, and everything will function fine.
Step 1:
- Open the Microsoft Excel file in which your data is located.
- Take your preferred cell.
- Enter the formula “=SUBSTITUTE(A1,” “,”, “,1)” After the formula has been entered, all you need to do is press enter to see the results.
Step 2: all that is left to do is drag the Autofill option to drag the handle to the end of your date range, and it will add a comma after each name.
Both of these methods replace a previously written text with a fresh piece of text. The most significant distinction between the two is as follows:
- When you know the term you want to modify, you should utilize the SUBSTITUTE function to make the change.
- When you know the location and length of the characters you wish to replace, you should use the REPLACE function to make the changes.
Learn more about the REPLACE function to comprehend the distinction in more depth!
Using the left function to Add commas
We may also do our goal by using the left function. However, this is a more complicated formula. We will not go into the specifics of the formula since doing so will make this strategy seem much more complicated than it is. All that is required is to memorize the formula and modify the cell ranges between the formulas to utilize it with your data collection. Similarly, the formula is lengthy: “=LEFT(A1,FIND(” “,A1,1-1)&”,”&MID(A1,FIND(” “,A1,1),255)”; here, A1 is the cell range, and you will simply need to modify it to correspond to your own data set. The only thing you will need to do is replace the A1s with C9s in your data set if your data is in the C9 cell. After that, you will be ready to add commas after the first word in your data set.
Step 1: Open your spreadsheet in which you want to add a comma after the first word in each cell. Adjust the formula to your data set and enter the formula “=LEFT(A1,FIND(” “,A1,1)-1)&”,”&MID(A1,FIND(” “,A1,1),255)” on the right adjoining cell.
Step 2: Press enter to see the results, and after that, drag the Autofill handles down to the end range of your data, and you will see the results.
Conclusion:
In this lesson, you learned about the many methods for inserting a comma after the first word in each cell in Microsoft Excel. When dealing with extensive datasheets, manually inserting a comma is not always possible or practical. With the aid of our function and formulae shown in this post, you will be able to add commas to your data sets in Excel, regardless of the size of your data sets. Just be sure you insert the formulae in the proper order. If you have difficulties remembering this formula, write it down somewhere.