Concatenate many cells without zero's

[art]

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
A11 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.

 

[Jacob Skaria]

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

 

[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

 

[Dave Peterson]

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

 

[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

 

[art]

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


Art.

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]

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.

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

Art.

 

[art]

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.

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.

 

[Dave Peterson]

What version of the function are you using?

What's in A1:A10?

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.

 

[art]

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.