# How to convert a string of comma-separated values into an array

I have a several cells with strings such as this one:

"John,Carla,Peter,John,Bob,Chris,Carla,Andrea"

I would like to count how many different people are named (i.e. how many unique values). The count for the string above would be 6. Note that the total amount of names in the strings can vary a lot. Some have only one name and the biggest one has 14 (where some can be duplicated). Some names have spaces in them (ex. "Peter Smith,Andrea,Carla Moore"), but we can rely on the commas to separate them.

I think the solution is to convert the string into an array and then use a combination of the formulas SUM, IF and FREQUENCY which I found online here, applied to that array.

However I don't know how to convert the string into an array (if that is the way to go).

• Did you try something? – Toto Mar 3 '18 at 17:22
• I found this here ozgrid.com/forum/forum/help-forums/excel-general/… and tried to use it but couldn't understand it. Then tried to split each string using the "Text to columns" function, and after that using that online tutorial I mention for counting values in an array, but I would have to enter the formula manually in each row otherwise the array doesn't change to next row. – PrincessLilly Mar 3 '18 at 17:42

Try this small User Defined Function:

``````Public Function PeopleKounter(s As String) As Long
Dim DQ As String, c As Collection
Set c = New Collection
DQ = Chr(34)

ary = Split(Replace(s, DQ, ""), ",")

On Error Resume Next
For Each a In ary
Next a
On Error GoTo 0

PeopleKounter = c.Count
End Function
`````` • I've now added "Application.Volatile" at the top to allow this function to automatically refresh when the worksheet is recalculated. – PrincessLilly Mar 3 '18 at 22:12
• @PrincessLilly Good idea..................................... – Gary's Student Mar 3 '18 at 22:34

This formula will iterate the "words" and only count the Unique.

``````=SUMPRODUCT(--(ISERROR(FIND(TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",99)),(ROW(\$1:\$17)-1)*99+1,99)),TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",99)),1,(ROW(\$1:\$17)-1)*99+1))))))
``````

As it iterates it compares it to the current with all that came before with `FIND()`. And error is returned if not found and thus it is counted. • Clever use of `SUMPRODUCT()` – Gary's Student Mar 3 '18 at 20:08
• Good option for someone who is looking to use a formula that's not user defined. – PrincessLilly Mar 3 '18 at 22:09