I had a question on how to do the following:
Is there any easy way to concatenate many cells to one cell. I have row from
A1:P1 with information in them. I want to connect them together in one long
string. Is there an easier way to do this, other than using the formula
concatenate and inserting each cell, or the "&" function? (it is to
cumbersome and I think excessive)

I got a response from "Jacob Skaria":

Why not use a UDF..Launch VBE using short-key Alt+F11. Insert a module and
paste the below code..

A1= "To be"
B1 = "merged"
C1 = JoinString(A1:B1," ")


Function JoinString(varRange As Range, Optional varDelimiter As String)
Dim varTemp As Range
For Each varTemp In varRange
JoinString = JoinString & varDelimiter & varTemp
Next
If varDelimiter < vbNullString Then
JoinString = Mid(JoinString, 2)
End If
End Function

However, I have some cells that have zero values in them Is there
a way to change the VBA code to ignore the zero values. So for example, I
want to use the formula like this,

A1 B1 C1 D1 E1
F1 G1
1.29 2.29 3.39 4.99 0 0
0

JoinString(A1:G1,", "), it should give me: 1.29, 2.29, 3.39, 4.99

And that's it, no zero values.

Thank you for your prompt response.

Art.

Function JoinString(varRange As Range, Optional varDelimiter As String)
Dim varTemp As Range
For Each varTemp In varRange
If varTemp < 0 Then
JoinString = JoinString & varDelimiter & varTemp
End If
Next
If varDelimiter < vbNullString Then
JoinString = Mid(JoinString, 2)
End If
End Function

--
If this post helps click Yes
---------------
Jacob Skaria


"art" wrote:

I had a question on how to do the following:
Is there any easy way to concatenate many cells to one cell. I have row from
A1:P1 with information in them. I want to connect them together in one long
string. Is there an easier way to do this, other than using the formula
concatenate and inserting each cell, or the "&" function? (it is to
cumbersome and I think excessive)

I got a response from "Jacob Skaria":

Why not use a UDF..Launch VBE using short-key Alt+F11. Insert a module and
paste the below code..

A1= "To be"
B1 = "merged"
C1 = JoinString(A1:B1," ")


Function JoinString(varRange As Range, Optional varDelimiter As String)
Dim varTemp As Range
For Each varTemp In varRange
JoinString = JoinString & varDelimiter & varTemp
Next
If varDelimiter < vbNullString Then
JoinString = Mid(JoinString, 2)
End If
End Function

However, I have some cells that have zero values in them Is there
a way to change the VBA code to ignore the zero values. So for example, I
want to use the formula like this,

A1 B1 C1 D1 E1
F1 G1
1.29 2.29 3.39 4.99 0 0
0

JoinString(A1:G1,", "), it should give me: 1.29, 2.29, 3.39, 4.99

And that's it, no zero values.

Thank you for your prompt response.

Art.

Thanks. Can you please also modify the code so it gives you all the amount in
a number format like 9.00 and not 9?


Thanks.

Art



"Jacob Skaria" wrote:

Function JoinString(varRange As Range, Optional varDelimiter As String)
Dim varTemp As Range
For Each varTemp In varRange
If varTemp < 0 Then
JoinString = JoinString & varDelimiter & varTemp
End If
Next
If varDelimiter < vbNullString Then
JoinString = Mid(JoinString, 2)
End If
End Function

--
If this post helps click Yes
---------------
Jacob Skaria


"art" wrote:

I had a question on how to do the following:
Is there any easy way to concatenate many cells to one cell. I have row from
A1:P1 with information in them. I want to connect them together in one long
string. Is there an easier way to do this, other than using the formula
concatenate and inserting each cell, or the "&" function? (it is to
cumbersome and I think excessive)

I got a response from "Jacob Skaria":

Why not use a UDF..Launch VBE using short-key Alt+F11. Insert a module and
paste the below code..

A1= "To be"
B1 = "merged"
C1 = JoinString(A1:B1," ")


Function JoinString(varRange As Range, Optional varDelimiter As String)
Dim varTemp As Range
For Each varTemp In varRange
JoinString = JoinString & varDelimiter & varTemp
Next
If varDelimiter < vbNullString Then
JoinString = Mid(JoinString, 2)
End If
End Function

However, I have some cells that have zero values in them Is there
a way to change the VBA code to ignore the zero values. So for example, I
want to use the formula like this,

A1 B1 C1 D1 E1
F1 G1
1.29 2.29 3.39 4.99 0 0
0

JoinString(A1:G1,", "), it should give me: 1.29, 2.29, 3.39, 4.99

And that's it, no zero values.

Thank you for your prompt response.

Art.

If you've the cells to be concatenated are formatted nicely, you could use this:

Option Explicit
Function JoinString(varRange As Range, Optional varDelimiter As String)
Dim varTemp As Range
For Each varTemp In varRange.Cells
JoinString = JoinString & varDelimiter & varTemp.Text
Next varTemp
If varDelimiter < vbNullString Then
JoinString = Mid(JoinString, Len(varDelimiter) + 1)
End If
End Function




art wrote:

Thanks. Can you please also modify the code so it gives you all the amount in
a number format like 9.00 and not 9?

Thanks.

Art

"Jacob Skaria" wrote:

Function JoinString(varRange As Range, Optional varDelimiter As String)
Dim varTemp As Range
For Each varTemp In varRange
If varTemp < 0 Then
JoinString = JoinString & varDelimiter & varTemp
End If
Next
If varDelimiter < vbNullString Then
JoinString = Mid(JoinString, 2)
End If
End Function

--
If this post helps click Yes
---------------
Jacob Skaria


"art" wrote:

I had a question on how to do the following:
Is there any easy way to concatenate many cells to one cell. I have row from
A1:P1 with information in them. I want to connect them together in one long
string. Is there an easier way to do this, other than using the formula
concatenate and inserting each cell, or the "&" function? (it is to
cumbersome and I think excessive)

I got a response from "Jacob Skaria":

Why not use a UDF..Launch VBE using short-key Alt+F11. Insert a module and
paste the below code..

A1= "To be"
B1 = "merged"
C1 = JoinString(A1:B1," ")


Function JoinString(varRange As Range, Optional varDelimiter As String)
Dim varTemp As Range
For Each varTemp In varRange
JoinString = JoinString & varDelimiter & varTemp
Next
If varDelimiter < vbNullString Then
JoinString = Mid(JoinString, 2)
End If
End Function

However, I have some cells that have zero values in them Is there
a way to change the VBA code to ignore the zero values. So for example, I
want to use the formula like this,

A1 B1 C1 D1 E1
F1 G1
1.29 2.29 3.39 4.99 0 0
0

JoinString(A1:G1,", "), it should give me: 1.29, 2.29, 3.39, 4.99

And that's it, no zero values.

Thank you for your prompt response.

Art.



--

Dave Peterson

I dropped your test for 0.

Option Explicit
Function JoinString(varRange As Range, Optional varDelimiter As String)
Dim varTemp As Range
For Each varTemp In varRange.Cells
if vartemp.value < 0 then
JoinString = JoinString & varDelimiter & varTemp.Text
end if
Next varTemp
If varDelimiter < vbNullString Then
JoinString = Mid(JoinString, Len(varDelimiter) + 1)
End If
End Function

Dave Peterson wrote:

If you've the cells to be concatenated are formatted nicely, you could use this:

Option Explicit
Function JoinString(varRange As Range, Optional varDelimiter As String)
Dim varTemp As Range
For Each varTemp In varRange.Cells
JoinString = JoinString & varDelimiter & varTemp.Text
Next varTemp
If varDelimiter < vbNullString Then
JoinString = Mid(JoinString, Len(varDelimiter) + 1)
End If
End Function

art wrote:

Thanks. Can you please also modify the code so it gives you all the amount in
a number format like 9.00 and not 9?

Thanks.

Art

"Jacob Skaria" wrote:

Function JoinString(varRange As Range, Optional varDelimiter As String)
Dim varTemp As Range
For Each varTemp In varRange
If varTemp < 0 Then
JoinString = JoinString & varDelimiter & varTemp
End If
Next
If varDelimiter < vbNullString Then
JoinString = Mid(JoinString, 2)
End If
End Function

--
If this post helps click Yes
---------------
Jacob Skaria


"art" wrote:

I had a question on how to do the following:
Is there any easy way to concatenate many cells to one cell. I have row from
A1:P1 with information in them. I want to connect them together in one long
string. Is there an easier way to do this, other than using the formula
concatenate and inserting each cell, or the "&" function? (it is to
cumbersome and I think excessive)

I got a response from "Jacob Skaria":

Why not use a UDF..Launch VBE using short-key Alt+F11. Insert a module and
paste the below code..

A1= "To be"
B1 = "merged"
C1 = JoinString(A1:B1," ")


Function JoinString(varRange As Range, Optional varDelimiter As String)
Dim varTemp As Range
For Each varTemp In varRange
JoinString = JoinString & varDelimiter & varTemp
Next
If varDelimiter < vbNullString Then
JoinString = Mid(JoinString, 2)
End If
End Function

However, I have some cells that have zero values in them Is there
a way to change the VBA code to ignore the zero values. So for example, I
want to use the formula like this,

A1 B1 C1 D1 E1
F1 G1
1.29 2.29 3.39 4.99 0 0
0

JoinString(A1:G1,", "), it should give me: 1.29, 2.29, 3.39, 4.99

And that's it, no zero values.

Thank you for your prompt response.

Art.



--

Dave Peterson

--

Dave Peterson

Your code gives me the same results as 9.2 and not 9.20. Thanks for your help.


Art.



"Dave Peterson" wrote:

I dropped your test for 0.

Option Explicit
Function JoinString(varRange As Range, Optional varDelimiter As String)
Dim varTemp As Range
For Each varTemp In varRange.Cells
if vartemp.value < 0 then
JoinString = JoinString & varDelimiter & varTemp.Text
end if
Next varTemp
If varDelimiter < vbNullString Then
JoinString = Mid(JoinString, Len(varDelimiter) + 1)
End If
End Function

Dave Peterson wrote:

If you've the cells to be concatenated are formatted nicely, you could use this:

Option Explicit
Function JoinString(varRange As Range, Optional varDelimiter As String)
Dim varTemp As Range
For Each varTemp In varRange.Cells
JoinString = JoinString & varDelimiter & varTemp.Text
Next varTemp
If varDelimiter < vbNullString Then
JoinString = Mid(JoinString, Len(varDelimiter) + 1)
End If
End Function

art wrote:

Thanks. Can you please also modify the code so it gives you all the amount in
a number format like 9.00 and not 9?

Thanks.

Art

"Jacob Skaria" wrote:

Function JoinString(varRange As Range, Optional varDelimiter As String)
Dim varTemp As Range
For Each varTemp In varRange
If varTemp < 0 Then
JoinString = JoinString & varDelimiter & varTemp
End If
Next
If varDelimiter < vbNullString Then
JoinString = Mid(JoinString, 2)
End If
End Function

--
If this post helps click Yes
---------------
Jacob Skaria


"art" wrote:

I had a question on how to do the following:
Is there any easy way to concatenate many cells to one cell. I have row from
A1:P1 with information in them. I want to connect them together in one long
string. Is there an easier way to do this, other than using the formula
concatenate and inserting each cell, or the "&" function? (it is to
cumbersome and I think excessive)

I got a response from "Jacob Skaria":

Why not use a UDF..Launch VBE using short-key Alt+F11. Insert a module and
paste the below code..

A1= "To be"
B1 = "merged"
C1 = JoinString(A1:B1," ")


Function JoinString(varRange As Range, Optional varDelimiter As String)
Dim varTemp As Range
For Each varTemp In varRange
JoinString = JoinString & varDelimiter & varTemp
Next
If varDelimiter < vbNullString Then
JoinString = Mid(JoinString, 2)
End If
End Function

However, I have some cells that have zero values in them Is there
a way to change the VBA code to ignore the zero values. So for example, I
want to use the formula like this,

A1 B1 C1 D1 E1
F1 G1
1.29 2.29 3.39 4.99 0 0
0

JoinString(A1:G1,", "), it should give me: 1.29, 2.29, 3.39, 4.99

And that's it, no zero values.

Thank you for your prompt response.

Art.



--

Dave Peterson

--

Dave Peterson