Help with blank cells when concatenating

[LM]

I have a list of 2000 rows by 25 columns. I want to concatenate a cells e to
y in each row. Not all cells have data in them but the cells that do have
data in them have a seven digit number.

I need to concatenate all cells with data in them and have them end up in
one column, with each seven digit number divided by a comma, i.e.
1234567,8912345,6789123,4567890.

If I use &","& I end up with extra commas where I have had blank cells.

I tried using spaces when concatenating, i.e. &" "&, removing all trailing
spaces using TRIM and then replacing the remaining spaces with commas but, no
matter how I try to format the column as text, as soon as I replace all the
spaces with commas, Excel turns most of them into numbers that look like this
- 2.02411E+62 and I can't get that back to appearing as a number or text with
commas between each seven numbers.

I tried using the following formula:

=substitute(trim(C2&" "&D2&" "&E2&" "&F2)," ",", ")

which I found in relation to a similar question in this discussion group but
I can't get that formula to work (obviously I have the equations going up to
&Y2) and I can't work out why even that formula won't work.

Any help would be so gratefully appreciated as I have so far spent hours on
this.

Lyn.

 

[Don Guillett]

This should do it. The 6 refers to column F which is one column to the left
of the 1st column. So, if your data starts in col B and you want the new
string in col a, use 1 instead of 6. Correct if you have word-wrap.

Sub joinwithoutblanks()
mc = 6
For c = 3 To Cells(Rows.Count, mc + 1).End(xlUp).row
Cells(c, mc) = ""
For i = mc + 1 To Cells(c, Columns.Count).End(xlToLeft).Column
If Cells(c, i) <> "" Then
Cells(c, mc) = Cells(c, mc) & "," & Cells(c, i)
End If
Next i
Cells(c, mc) = "'" & Right(Cells(c, mc), Len(Cells(c, mc)) - 1)
Next c
End Sub

 

[Teethless mama]

Download and install the free add-in Morefunc.xll from:
http://xcell05.free.fr/english/

....then use this formula

=SUBSTITUTE(TRIM(MCONCAT(IF(C2:Z2<>"",C2:Z2&" ","")))," ",",")

ctrl+shift+enter, not just enter

 

[Ron Rosenfeld]

Download and install the free add-in Morefunc.xll from:
http://xcell05.free.fr/english/

...then use this formula

Unfortunately, that link has been down for a number of weeks for me. Are you
able to access it?
--ron

 

[LM]

Thanks to both of you. I will try your suggestions tomorrow.

In the meantime, I tried SUBSTITUTE and that seemed to substitute commas for
my spaces and gave me the result I wanted. I am sure I had tried this before
and it didn't work but obviously I did something wrong the first time.

Thanks again.

 

[LM]

No, I can't access it either. I receive an error message - and it's
certainly not in English!!

 

[Gord Dibben]

Function ConCatRange(CellBlock As Range) As String
Dim Cell As Range
Dim sbuf As String
For Each Cell In CellBlock
If Len(Cell.text) > 0 Then sbuf = sbuf & Cell.text & ","
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

=ConCatRange(E1:Y1) and copy down.


Gord Dibben MS Excel MVP

 

[Ron Rosenfeld]

Download and install the free add-in Morefunc.xll from:
http://xcell05.free.fr/english/

...then use this formula

=SUBSTITUTE(TRIM(MCONCAT(IF(C2:Z2<>"",C2:Z2&" ","")))," ",",")

ctrl+shift+enter, not just enter

Oh, if he can get morefunc, I think this would work as well, and be shorter:

=SUBSTITUTE(TRIM(MCONCAT(C2:Z2," "))," ",",")
--ron

 

[Rick Rothstein \(MVP - VB\)]

You might want to consider this minor change to your code so that it returns
the empty string if there is nothing in any of the cells rather than having
the function "error out" if that is the case...

Function ConCatRange(CellBlock As Range) As String
Dim Cell As Range
Dim sbuf As String
For Each Cell In CellBlock
If Len(Cell.Text) > 0 Then sbuf = sbuf & Cell.Text & ","
Next
If Len(sbuf) > 0 Then ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

For the more general case (this does not apply to to the OP), if the number
of cells in the CellBlock are very large, the repeated concatenations can
become inefficient as the execution nears the end of the loop. This code may
be more efficient for this situation (not sure as I haven't attempted to
time it) as it avoids concatenations altogether (although I would guess it
is not as efficient as your function for smaller number of cells due to the
ReDim Preserve and Join function calls)...

Function ConCatRange(CellBlock As Range) As String
Dim X As Long
Dim Z As Long
ReDim Content(1 To CellBlock.Count) As String
For X = 1 To UBound(Content)
If Len(CellBlock(X).Value) > 0 Then
Z = Z + 1
Content(Z) = CellBlock(X).Value
End If
Next
If Z > 0 Then
ReDim Preserve Content(1 To Z)
ConCatRange = Join(Content, ",")
End If
End Function

Rick

 

[Gord Dibben]

Thanks for the input Rick.

Have updated per your suggestion.


Gord

 

[sajay]

dear lyn

try this
=SUBSTITUTE(A2&","&B2&","&C2&","&D2&","&E2&","&F2&","&G2 upto 25th column
,",,",",")

 

[Spiky]

Perhaps this would help?

http://www.download.com/Morefunc/3000-2077_4-10423159.html