In Excel, a time comes when we are required to separate text strings using a comma. Think of a situation where you have a lot of data and you want to split it into columns. The best way to go about it is to separate the data by a comma. In this post, we will look at the simple steps you need to follow in order to separate cells by comma.
Figure 1: Splitting cells by comma
Note that when you want to separate cells by a comma, the first thing you have to do is to locate the delimiter. But this would be quite a hustle especially if you are dealing with thousands of data sets to that you want to convert column to comma separated list. The best way to go about it is by using the case sensitive SEARCH or case sensitive FIND.
After finding the delimiters’ position, you can now use the RIGHT, LEFT or MID functions to get the part of the corresponding text string.
In our example in figure 1 above, we have the item name, color of the item as well as the size. We want to separate the list by comma so as to have item, color and size in different columns.
To achieve our goal, we have used the following formula to get the first part name.
=LEFT (A2, SEARCH(“-”, A2,1)-1)
The SEARCH function will determine the position of the first delimiter and thus create a column for the item name.
This way, we have created a comma separated values into columns.