When you deal with several cells inside your spreadsheet data, adding commas manually in particular places inside cells may be a good decision, but when you deal with a huge amount of data you have to find a better way to do it.
This is where formulas come into play.
Add a comma after the first word
Let’s use this example:
Here, we have a list of names: last name and first name. In this case, there should be a comma after the first word, otherwise, someone might confuse the name with the name.
We are going to use two formulas that return the same result.
The SUBSTITUTE function replaces one part of a string with another string.
=SUBSTITUTE(A2," ",", ")
This formula is going to replace ” ” with “, “. In other words, it will add comma and space where there are spaces inside our example.
It works great, but look at what happens if there is somebody with two names.
There are many cases that this is what you need, but in our case, we want Excel to place a single comma, after the first word.
That’s easy, all you need to do is to add is an optional parameter to the SUBSTITUTE function.
This parameter specifies how many times you want to make a substitute. Because we want to add a single comma, therefore we add number 1.
=SUBSTITUTE(A2," ",", ", 1)
After this change, our example is going to look like this:
REPLACE and FIND
It’s going to return the same result as the formula with the SUBSTITUTE function.
Before I explain how the REPLACE function works, let’s see what FIND does in our example.
Type this formula into cell B2, and autofill for the rest of the cells:
The FIND function returns the position of the first argument in a cell. In our case, the first occurrence of space.
Now, when we know that, let’s analyze the whole formula.
First, the REPLACE function takes a text to change.
In the second argument, the FIND function returns a position of the first occurrence of space.
The third argument specifies how many characters we want to replace. We don’t want to replace any characters, because we just want to insert a comma before space.
In the last argument, we have to specify which character we want to insert. It’s a comma in our case.