如何使用单元格公式（含SUBSTITUTE函数）为逗号分隔值添加引号？

Hey there! Let’s tackle your questions head-on—yes, you absolutely can wrap comma-separated values in quotes, and we can pull this off neatly with the SUBSTITUTE function (plus a little extra to handle the start and end quotes).

The Formula You Need #

If your original text lives in cell A1 (like sun, sky, cloud, clouds), use this formula to get the quoted format you’re after:

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

How It Works (Step-by-Step Breakdown) #

Let’s unpack this so you understand every piece:

• CHAR(34): This is Excel’s clean way to insert a double quote—no need to type two quotes (which is how Excel escapes them manually). It makes the formula way easier to read.
• SUBSTITUTE(A1, ", ", CHAR(34)&", "&CHAR(34)): This targets every , (comma plus space) in your original text and replaces it with ", " (quote, comma, space, quote). This handles wrapping the end of one value and setting up the start of the next.
• The outer CHAR(34)&...&CHAR(34) adds the opening quote at the very beginning and closing quote at the very end of the entire string.

Example Output #

For the input sun, sky, cloud, clouds, the formula will spit out exactly what you want:

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

Edge Case Handling #

Even if you have a single value (like sun with no commas), this formula still works flawlessly—it’ll return "sun" without any hiccups.

If you prefer using literal quotes instead of CHAR(34), here’s the equivalent formula (note the double quotes for escaping):

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

内容的提问来源于stack exchange，提问作者tectomics