CODEWITHSUNDEEP
excelvbastring
JamesDev
JamesDev

Reputation: 243

Inserting semicolon in between data in a cell in Excel

I have data in column A, C, E and G. I want column I to hold all of the data from these separated by semicolons, I have searched the web but all I find is how to replace line breaks with semicolons and that doesn't do it. Below are the 3 pieces of code I have attempted to work into it

Range("I" & i).Value = "=A" & i & Chr(59) & "&" & "C" & i & "&" & "E" & i & "&" & "G" & i
Range("I" & i).Value = "=A" & i & "&C" & i & "&E" & i & "&G" & i
Range("I" & i).Value = Range("A" & i).Value + ; + Range("C" & i).Value + ; + Range("E" & i).Value + ; + Range("G" & i).Value

The second line comes closest to what I want but when I add & ";" into it I get errors.

Any ideas thoroughly appreciated

SOLVED : This was solved by an answer below, I had managed my own fix which I will detail but the accepted answer is a cleaner, more efficient way

mystring = "=A" & i & "&" & Chr(34) & ";" & Chr(34) & "& C" & i & "&" & Chr(34) & ";" & Chr(34) & "& E" & i & "&" & Chr(34) & ";" & Chr(34) & "& G" & i Range("I" & i).Value = mystring

Upvotes: 1

Views: 14905

Answers (2)

shahkalpesh
shahkalpesh

Reputation: 33474

=CONCATENATE(A1, ";", C1, ";", E1, ";", G1)

Using VBA

Range("A2").FORMULA = REPLACE("=CONCATENATE(A1, ';', C1, ';', E1, ';', G1)", "'", CHR(34))

Upvotes: 0

Dante May Code
Dante May Code

Reputation: 11247

You should use "&"";""".

In detail, you should use something like

"=A" & i & "&"";""" & "&C" & i

which result in

=A1&";"&C1

suppose i = 1.

Upvotes: 4

Related Questions