Solved: add - when merge data on cell of column

[parscon]

I need a help , I have this code .Now this code will merge all of data on column G to column M1 and my problem when i merge data on column M1 i want to add - between each data . that mean i want :

data1-dat2-data3

Now this code make

data1data2data2







Dim v As Variant
Dim s As String
Dim i As Long

' Fetch from sheet
v = Range("G1:G2000").Value
' Concatenate into a single string
s = ""
For i = LBound(v, 1) To UBound(v, 1)
s = s & v(i, 1)
Next i
' Put back on sheet
Range("M1").Value = s
End With

[Bob Phillips]

Dim v As Variant

' Fetch from sheet
v = Application.Transpose(Range("G1:G2000").Value)
' Concatenate into a single string and put back on sheet
Range("M1").Value = Join(v, "-")

[parscon]

Thank you very much for your help but when i run your code add - to empty cell also



-------------------------------------------------11039409-11039247-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------



I want just add - to filed with data

[Bob Phillips]

Dim v As Variant

' Fetch from sheet
v = Application.Transpose(Range("G1:G2000").Value)
' Concatenate into a single string and put back on sheet
With Range("M1")

.Value = Join(v, "-")
Do While InStr(.Value, "--") > 0

.Value = Replace(.Value, "--", "-")
Loop
End With

[parscon]

Thank you again , just i have a question . can you add a code to delete the first - and also the latest - ? now after run your code my data will be like this :




-11039409-11039247-112373809-



and for 1 cell show me : -11039193-


Please help me for this also .

[parscon]

Option Explicit
Sub RemoveSquareBrackets()
Dim c As Range
Application.ScreenUpdating = False
For Each c In Range("G1", Range("G" & Rows.Count).End(xlUp))
If Left(c, 1) = "-" Then c = Mid(c, 2, Len(c) - 1)
If Right(c, 1) = "-" Then c = Left(c, Len(c) - 1)
Next c
Application.ScreenUpdating = True
End Sub



THank you .

[Bob Phillips]

There is no need to use that loop



Dim v As Variant

' Fetch from sheet
v = Application.Transpose(Range("G1:G2000").Value)
' Concatenate into a single string and put back on sheet
With Range("M1")

.Value = Join(v, "-")
Do While InStr(.Value, "--") > 0

.Value = Replace(.Value, "--", "-")
Loop
.Value = Replace("-" & .Value & "-", "--", "")
End With

[parscon]

Thank you so much