Concatenate many cells without zero's
 

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.

Concatenate many cells without zero's
 

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.

Concatenate many cells without zero's
 

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.

Concatenate many cells without zero's
 

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

Concatenate many cells without zero's
 

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

Concatenate many cells without zero's
 

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

Concatenate many cells without zero's
 

Actually, it gives you want's displayed in the cell on the worksheet. If you
change the format to show what you want, you can use that code.

If you don't want to change the cell's format, you can do the same thing in
code:

JoinString = JoinString & varDelimiter & format(varTemp.value,"0.00")

Use the format that you want--include as many decimals, leading zeros, commas,
currency symbols as you want.


art wrote:

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


--

Dave Peterson

Concatenate many cells without zero's
 

Right. Got it. But now it formats it so there is a space between each number
7 spaces. What do I have to change so there is no or only one space in
between. (really, the best should be what ever I put in the formula, like
=joinstring(A1:A10,", ") and thats it).

Thanks for your help.

Art.

"Dave Peterson" wrote:

Actually, it gives you want's displayed in the cell on the worksheet. If you
change the format to show what you want, you can use that code.

If you don't want to change the cell's format, you can do the same thing in
code:

JoinString = JoinString & varDelimiter & format(varTemp.value,"0.00")

Use the format that you want--include as many decimals, leading zeros, commas,
currency symbols as you want.


art wrote:

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


--

Dave Peterson

Concatenate many cells without zero's
 

What version of the function are you using?

What's in A1:A10?



art wrote:

Right. Got it. But now it formats it so there is a space between each number
7 spaces. What do I have to change so there is no or only one space in
between. (really, the best should be what ever I put in the formula, like
=joinstring(A1:A10,", ") and thats it).

Thanks for your help.

Art.

"Dave Peterson" wrote:

Actually, it gives you want's displayed in the cell on the worksheet. If you
change the format to show what you want, you can use that code.

If you don't want to change the cell's format, you can do the same thing in
code:

JoinString = JoinString & varDelimiter & format(varTemp.value,"0.00")

Use the format that you want--include as many decimals, leading zeros, commas,
currency symbols as you want.


art wrote:

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


--

Dave Peterson


--

Dave Peterson

Concatenate many cells without zero's
 

Oh right. Sorry, I forgot about the last part you gave me. JoinString =
JoinString & varDelimiter & format(varTemp.value,"0.00").


Thanks so much. It works great.

Thanks Dave.

Art.

"Dave Peterson" wrote:

What version of the function are you using?

What's in A1:A10?



art wrote:

Right. Got it. But now it formats it so there is a space between each number
7 spaces. What do I have to change so there is no or only one space in
between. (really, the best should be what ever I put in the formula, like
=joinstring(A1:A10,", ") and thats it).

Thanks for your help.

Art.

"Dave Peterson" wrote:

Actually, it gives you want's displayed in the cell on the worksheet. If you
change the format to show what you want, you can use that code.

If you don't want to change the cell's format, you can do the same thing in
code:

JoinString = JoinString & varDelimiter & format(varTemp.value,"0.00")

Use the format that you want--include as many decimals, leading zeros, commas,
currency symbols as you want.


art wrote:

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


--

Dave Peterson


--

Dave Peterson