Prev: How to get result by using CountIF, if there are 3 conditions to b
Next: Help with a bsic formula
From: LM on 5 Jul 2008 11:11
No, I can't access it either. I receive an error message - and it's
certainly not in English!!

"Ron Rosenfeld" wrote:

> On Sat, 5 Jul 2008 07:09:01 -0700, Teethless mama
> <Teethlessmama(a)discussions.microsoft.com> wrote:
>
> >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
>
From: Gord Dibben on 5 Jul 2008 11:12
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

On Sat, 5 Jul 2008 05:26:01 -0700, LM <LM(a)discussions.microsoft.com> wrote:

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

From: Ron Rosenfeld on 5 Jul 2008 11:14
On Sat, 5 Jul 2008 07:09:01 -0700, Teethless mama
<Teethlessmama(a)discussions.microsoft.com> wrote:

>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
From: Rick Rothstein (MVP - VB) on 5 Jul 2008 14:11
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" <gorddibbATshawDOTca> wrote in message
news:vm3v641dqga0f89ipjtv11l93ksn6uq2bo(a)4ax.com...
> 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
>
> On Sat, 5 Jul 2008 05:26:01 -0700, LM <LM(a)discussions.microsoft.com>
> wrote:
>
>>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.
>

From: Gord Dibben on 6 Jul 2008 11:34
Thanks for the input Rick.

Have updated per your suggestion.


Gord

On Sat, 5 Jul 2008 14:11:02 -0400, "Rick Rothstein \(MVP - VB\)"
<rick.newsNO.SPAM(a)NO.SPAMverizon.net> wrote:

>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" <gorddibbATshawDOTca> wrote in message
>news:vm3v641dqga0f89ipjtv11l93ksn6uq2bo(a)4ax.com...
>> 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
>>
>> On Sat, 5 Jul 2008 05:26:01 -0700, LM <LM(a)discussions.microsoft.com>
>> wrote:
>>
>>>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.
>>

First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: How to get result by using CountIF, if there are 3 conditions to b
Next: Help with a bsic formula