When dealing with a large number of cells in your spreadsheet data, manually inserting commas at specified locations inside cells may be a smart idea. It is very common for there to be no commas in the data after the first word of a list generated at random in Excel. This is especially true when the information is copied from the word editor. However, when dealing with a large amount of data, you must find a better technique to accomplish it or devise a unique direction to do it manually.
If you need to put a comma after the first letter in each cell in Excel, you can use one of the ways listed below. These are the: 1) Using REPLACE function. 2) Using the SUBSTITUTE function.
Using REPLACE Function
When you recognize the place and length of the letters whose look you want to modify, utilize the REPLACE function. When you wish to modify their look, this is the situation.
The following is the formula for adding a comma after the first word:
=REPLACE (the cell in which comma will be added, FIND(” “, the cell in which comma will be added),0,”,”)
Let’s look at how to use the REPLACE function to put a comma after the first letter/word in a cell.
Step 1
In an Excel spreadsheet, we have the following information. See the picture below, where the comma will be inserted.
In one cell of our spreadsheet, we have some worker identities and IDs.
Step 2
Through using the formula below, we wish to split the worker name and Id with a comma.
=REPLACE(A1,FIND(” “,A1),0,”,”)
Refer to the image below to see how it’s done.
In this example, first, choose one cell and then apply the aforementioned procedure to separate commas. Entering the formula yields the following result.
In this case, the FIND function will return the position in the input phrase “Name EmpID” which matches the first space word. Given the answer of 5, it is safe to assume that the first space in “Name” may be located in the fifth place. Following that, Name, EmpID should be the final output after using the REPLACE function to relocate the comma to the 5th position in “Name, EmpId,” as seen above.
Step 3
To replicate this formula in other cells, pick the B1 cell and drag it to the chosen column below the B1 cell to obtain the desired result.
Using SUBSTITUTE Function
The SUBSTITUTE function in Excel is a method that may be used to search for and change specific text data. If you wish to replace an old word with a different one, this method will be quite useful. It adds new material in place of the previous word in a string of words when it replaces specific text in a string of text.
The formula for using the SUBSTITUTE function is shown below.
=SUBSTITUTE(cell, old text, new text, number of occurrences)
Step 1
To put a comma after the first message in the cell, we will replace the first instance of space with a comma (,). Look at the formula below.
=SUBSTITUTE(A1,” “,”, “,1)
See the example below for a better understanding.
Step 2
When you click Enter, the outcome will be “Name, EmpID.”
By choosing the B1 and moving it to the appropriate column below the B1 cell, you will get the correct result. The formula will now be mirrored in the other cells.
Conclusion
In this article, you studied how to use Excel’s REPLACE and SUBSTITUTE functions to add a comma after the first text in a cell. You can employ one of these two roles based on your needs as and when they arise.