How to add single quote and comma to all values in

问题:

I have a column host with following values:

server1.abc.com
server2.abc.com
server3

Now, I want each of these values to be single quotes and separated by comma. The output should be like as below:

'server1.abc.com','server2.abc.com','server3'

Please let me know how this can be achieved using Excel.

So far I am able to separate values using , with following code:

Public Function MergeME(r As Range) As String
  MergeME = Join(Application.Transpose(r.Value), ",")
End Function

How can I add double quotes around values in each cell??

回答1:

Your original UDF was close. You just need to join with ',' rather than , and then place ' at the start and end of your output string

Public Function MergeME(r As Range) As String
  MergeME = "'" & Join(Application.Transpose(r.Value), "','") & "'"
End Function

Output string as required:

'server1.abc.com','server2.abc.com','server3'

回答2:

With data in A1 through A3, in B1 enter:

="'"

(this is a single quote encased in double quotes)

Then in B2:

=B1 & A1 & B1 & "," & B1 & A2 & B1 & "," & B1 & A3 & B1

回答3:

NO functions or VBA are needed

In cell B1 input this = CHAR(39)
In cell C1 input this ,
In cell D1 = CONCAT($B$1,A1,$B$1,$C$1)
In cell D2 = CONCAT(D1,$B$1,A2,$B$1,$C$1)

from D2 - D(x) this is automated you can copy Cell D2 to Cell D3 yielding this:

in cell D3 = CONCAT(D2,$B$1,A3,$B$1,$C$1)

YOU COULD FIND THE LARGEST LENGTH in the Dx column via lookup and then get your final output string; i didn't do that here-- i added the following:

In cell E3 = LEN(D3) 

i take the final comma out:

In cell F2 = LEFT(D3,E3-1)

very automated task