Split values separated by commas using Sheet's function
Split values separated by commas using Sheet's function

/ faq

How Do I Use Data That Has Commas in It?

Subscribe to our newsletter

Get the latest news from Sapiengraph

Sometimes, Sapiengraph can return a list of things. For example, if you use one of the Contact Information formulas to get either someone's personal contact number or their personal email address, you might get multiple results if the person you're querying has multiple forms of contact via that medium.

When Sapiengraph returns a list, it will be a comma-separated list of values. That means that each item in the list will be printed in a row, with commas in between.

Here's an example of a comma-separated list of company names:

Apple, Microsoft, Google, Stripe

Let's say you have this value in A1. How can we separate it? There are two options.

Put values into multiple columns

If you want your results to be displayed in the next adjacent columns, you can enter the following formula into B1. Make sure that the next few columns are empty!

=SPLIT(A1, ",")

And here's the result. Notice that the formula is present in B1:

But there is no formula in C1. Google Sheets automatically put the string text here, no formula at all. Here's a screenshot showing the formula bar for C1:

Pick one value from the list

Let's say we only want the first value from the list, and we don't care about the others. This might be the right approach if you have a very dense spreadsheet, and one method of contact suffices.


Here's what we're doing with this formula:

  1. Split the cell based on the , character. But don't use this value yet, only remember it.
  2. Retrieve the item in the 0th row and 1st column and print it.
    • Why is it row 0 but column 1? Because column 0 holds the entire array. So the column here is 1-indexed.

We could also change the column index. For example, this formula would give us the 4th item:


Remember to convert to text

Don't forget to convert your cells to text when you're done, so that they don't get automatically refreshed by Google!

Subscribe to our newsletter

Get the latest news from Sapiengraph

Latest Articles

Here’s what we’ve been up to recently.