CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
HSHO
HSHO

Reputation: 525

Add Double Quotes before and after the string including Lines

I want to add inverted commas before and after the string and Lines by replacing the Comma. I have below string and using this for inverted commas.

Char(34)&A1&Char(34)

But when i add this formula to add lines then it adds double inverted commas. Any solution to this problem will be appreciated.

My formula =CHAR(34)&SUBSTITUTE(A1,",",CHAR(10))&CHAR(34) the string becomes like this

"""Completed      BDM 01-24-2019
 DDM 10-31-2021
 """

enter image description here

Sheet Link

Upvotes: 1

Views: 1100

Answers (1)

Tanaike
Tanaike

Reputation: 201473

In your situation, how about the following sample formula?

Sample formula:

=ARRAYFORMULA(Char(34)&TRIM(REGEXREPLACE(REGEXREPLACE(A2:A10," {2,}|, $"," "),",",CHAR(10)))&Char(34))
  • At first, the multiple spaces and the last comma are replaced with a single space. And the comma is replaced with CHAR(10). And, Char(34) is added to the 1st and last character.

Result:

enter image description here

Reference:

Added:

From your following replying,

But i just "want" these inverted commas 1 time at beginning and end of the string not twice. When i copy any cell from the formula and paste into the Word document in converts the inverted commas "From this" to ""this"" but i only want "this"

In the current stage, when the line break is included in the cell value, when the cell is copied and pasted, it seems that it becomes """test""". In that case, when you want to copy and paste the cell values like "test" instead of """test""", how about the following sample formula?

=ARRAYFORMULA(IF(REGEXMATCH(REGEXREPLACE(A2:A10,", $",""),","),TRIM(REGEXREPLACE(REGEXREPLACE(A2:A10," {2,}|, $"," "),",",CHAR(10))),Char(34)&TRIM(REGEXREPLACE(A2:A10," {2,}|, $"," "))&Char(34)))

In this sample formula, the double quotes are not shown in the cell. And, the value without the line break is enclosed by ". But when you copy and paste the cell value, the value is enclosed by the single double-quote.

Upvotes: 2

Related Questions