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.
Instant Connection to an Expert through our Excelchat Service
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
Leave a Comment