This is not an Excel feature, although it should be! Often times we have a list of things in a column that needs to be combined into, say, a comma-delimited string. Column to Text in R…
Column to Text in Excel
That column looks like this:
Typically I write a quick formula that cumulatively concacatenates the rows, something like this:
That last cell is what I want. Usually it’s to plug it into a SQL query or something.
Column to Text in R
This is super easy in R. Let’s say you have a vector like this:
col = c(156863,141205,481808,515823,648410,656289,900801)This vector could have been sourced from an existing data frame. We can use the paste() function which is used for combining strings.
string = paste(col, collapse = ", ")
string
length(string)[1] "156863, 141205, 481808, 515823, 648410, 656289, 900801" [1] 1
And there you see that after we apply the paste() function with collapse, we get a vector with one single value, and that value is a comma-separated string of customer IDs.
Now that string can be plugged into a SQL query or elsewhere.
Deduping that list
If we do need this for a SQL query (like a WHERE clause), we should probably remove duplicates. R really starts to shine here compared to Excel.
Just put unique(col) into the paste() function instead:
# collapse the col vector into a string
string = paste(unique(col), collapse = ", ")As with all nested functions, read from inside-out. First we get a unique vector of col values, and then we plug that into paste().