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
>

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

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
>

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

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
>