CODEWITHSUNDEEP
google-sheets
tectomics
tectomics

Reputation: 147

Is there a way to wrap a list of comma separated values from a cell in quotation marks?

Is there a cell formula I can use to produce the following with some SUBSTITUTE functions?

I've got data in a cell like:

sun, sky, cloud, clouds

and I'm trying to wrap each value in quotation marks to give me

"sun", "sky", "cloud", "clouds"

I'm struggling as there are multiple values.

Upvotes: 2

Views: 1180

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34180

I think this is enough - plz let me know if there is a case I haven't thought of:

=regexreplace(A1,"(\w+)","""$1""")

enter image description here

So Regexreplace works in a similar way to Substitute, but lets you specify what is to be replaced using a regular expression

In this example:

  • \w means any letter
  • + means repeat one or more times so \w+ represents a word.
  • ( ) means a capture group. Whatever is in the brackets will be captured and can be retrieved later.
  • $1 means retrieve the contents of the first (and only) capture group
  • "" stands for a single " (double-quote).

Rexegg.com is a useful reference.

You could modify this to include sun-screen and cloud's if you wanted to:

=regexreplace(A1,"([\w'-]+)","""$1""")

Upvotes: 2

MattKing
MattKing

Reputation: 7773

I think this should work:

=CHAR(34)&SUBSTITUTE(A1,", ",CHAR(34)&", "&CHAR(34))&CHAR(34)

with your data in cell A1. That will only work if you have spaces after each comma in the original data.

Upvotes: 2

Related Questions