Concatenate Text Strings over Array
 

Hello All-

I've made my own round-a-bout way to do this procedure, but every time I
have to change the array it takes a lot of work to correct. In this range;
I3:BD10 there is either text or a "/". I need to concatenate each line of
text and separate each value by a comma. For instance,

Red / Blue / = Red, Blue
/ / Blue / = Blue

What I've done is actually recorded myself going into the cells afterwards
in a helper column and concatenating all of them and then using find/replace
to remove extraneous commas and slashes. But, because of the restrictions to
how many cells you can concatenate I have to use 4 (!) helper columns.

Is there anyway to do this easier - I would want it to say-

"If any row in this array has text that is not a "/", concatenate it and
separate it by a comma in a certain cell."

Concatenate Text Strings over Array
 

Public Sub ProcessData()
Dim i As Long, j As Long
Dim iLastRow As Long
Dim iLastCol As Long
Dim cell As Range
Dim Sh As Worksheet

With ActiveSheet

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
iLastCol = .Cells(i, .Columns.Count).End(xlToLeft).Column
For j = 1 To iLastCol
If .Cells(i, j).Value < "/" Then
.Cells(i, iLastCol + 1).Value = _
.Cells(i, iLastCol + 1).Value & "," & _
.Cells(i, j).Value
End If
Next j
.Cells(i, iLastCol + 1).Value = _
Right(.Cells(i, iLastCol + 1).Value, Len(.Cells(i, iLastCol
+ 1).Value) - 1)
Next i
End With

End Sub

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Katie" wrote in message
...
Hello All-

I've made my own round-a-bout way to do this procedure, but every time I
have to change the array it takes a lot of work to correct. In this range;
I3:BD10 there is either text or a "/". I need to concatenate each line of
text and separate each value by a comma. For instance,

Red / Blue / = Red,
Blue
/ / Blue / = Blue

What I've done is actually recorded myself going into the cells afterwards
in a helper column and concatenating all of them and then using
find/replace
to remove extraneous commas and slashes. But, because of the restrictions
to
how many cells you can concatenate I have to use 4 (!) helper columns.

Is there anyway to do this easier - I would want it to say-

"If any row in this array has text that is not a "/", concatenate it and
separate it by a comma in a certain cell."

Concatenate Text Strings over Array
 

One way:

Public Function ConcatWithException( _
ByRef rng As Excel.Range) As String
Const csEXCEPTION As String = "/"
Const csDELIM As String = ", "
Dim rArea As Range
Dim rCell As Range
Dim sBuild As String
Dim sTemp As String
For Each rArea In rng
For Each rCell In rArea
sTemp = Trim(rCell.Text)
If sTemp < csEXCEPTION Then _
If Len(sTemp) 0 Then _
sBuild = sBuild & csDELIM & sTemp
Next rCell
Next rArea
If Len(sBuild) 0 Then
ConcatWithException = Mid(sBuild, Len(csDELIM) + 1)
Else
ConcatWithException = vbNullString
End If
End Function


In article ,
Katie wrote:

Hello All-

I've made my own round-a-bout way to do this procedure, but every time I
have to change the array it takes a lot of work to correct. In this range;
I3:BD10 there is either text or a "/". I need to concatenate each line of
text and separate each value by a comma. For instance,

Red / Blue / = Red, Blue
/ / Blue / = Blue

What I've done is actually recorded myself going into the cells afterwards
in a helper column and concatenating all of them and then using find/replace
to remove extraneous commas and slashes. But, because of the restrictions to
how many cells you can concatenate I have to use 4 (!) helper columns.

Is there anyway to do this easier - I would want it to say-

"If any row in this array has text that is not a "/", concatenate it and
separate it by a comma in a certain cell."

Concatenate Text Strings over Array
 

Bob-

How can I get this to only act on the specified range instead of the entire
sheet?

"Bob Phillips" wrote:

Public Sub ProcessData()
Dim i As Long, j As Long
Dim iLastRow As Long
Dim iLastCol As Long
Dim cell As Range
Dim Sh As Worksheet

With ActiveSheet

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
iLastCol = .Cells(i, .Columns.Count).End(xlToLeft).Column
For j = 1 To iLastCol
If .Cells(i, j).Value < "/" Then
.Cells(i, iLastCol + 1).Value = _
.Cells(i, iLastCol + 1).Value & "," & _
.Cells(i, j).Value
End If
Next j
.Cells(i, iLastCol + 1).Value = _
Right(.Cells(i, iLastCol + 1).Value, Len(.Cells(i, iLastCol
+ 1).Value) - 1)
Next i
End With

End Sub

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Katie" wrote in message
...
Hello All-

I've made my own round-a-bout way to do this procedure, but every time I
have to change the array it takes a lot of work to correct. In this range;
I3:BD10 there is either text or a "/". I need to concatenate each line of
text and separate each value by a comma. For instance,

Red / Blue / = Red,
Blue
/ / Blue / = Blue

What I've done is actually recorded myself going into the cells afterwards
in a helper column and concatenating all of them and then using
find/replace
to remove extraneous commas and slashes. But, because of the restrictions
to
how many cells you can concatenate I have to use 4 (!) helper columns.

Is there anyway to do this easier - I would want it to say-

"If any row in this array has text that is not a "/", concatenate it and
separate it by a comma in a certain cell."

Concatenate Text Strings over Array
 

These are awesome- now I'm just having trouble finding exactly where to put
in my range and cell data.

Thank you both!

"JE McGimpsey" wrote:

One way:

Public Function ConcatWithException( _
ByRef rng As Excel.Range) As String
Const csEXCEPTION As String = "/"
Const csDELIM As String = ", "
Dim rArea As Range
Dim rCell As Range
Dim sBuild As String
Dim sTemp As String
For Each rArea In rng
For Each rCell In rArea
sTemp = Trim(rCell.Text)
If sTemp < csEXCEPTION Then _
If Len(sTemp) 0 Then _
sBuild = sBuild & csDELIM & sTemp
Next rCell
Next rArea
If Len(sBuild) 0 Then
ConcatWithException = Mid(sBuild, Len(csDELIM) + 1)
Else
ConcatWithException = vbNullString
End If
End Function


In article ,
Katie wrote:

Hello All-

I've made my own round-a-bout way to do this procedure, but every time I
have to change the array it takes a lot of work to correct. In this range;
I3:BD10 there is either text or a "/". I need to concatenate each line of
text and separate each value by a comma. For instance,

Red / Blue / = Red, Blue
/ / Blue / = Blue

What I've done is actually recorded myself going into the cells afterwards
in a helper column and concatenating all of them and then using find/replace
to remove extraneous commas and slashes. But, because of the restrictions to
how many cells you can concatenate I have to use 4 (!) helper columns.

Is there anyway to do this easier - I would want it to say-

"If any row in this array has text that is not a "/", concatenate it and
separate it by a comma in a certain cell."

Concatenate Text Strings over Array
 

It does only work on the range in column A.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Katie" wrote in message
...
Bob-

How can I get this to only act on the specified range instead of the
entire
sheet?

"Bob Phillips" wrote:

Public Sub ProcessData()
Dim i As Long, j As Long
Dim iLastRow As Long
Dim iLastCol As Long
Dim cell As Range
Dim Sh As Worksheet

With ActiveSheet

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
iLastCol = .Cells(i, .Columns.Count).End(xlToLeft).Column
For j = 1 To iLastCol
If .Cells(i, j).Value < "/" Then
.Cells(i, iLastCol + 1).Value = _
.Cells(i, iLastCol + 1).Value & "," & _
.Cells(i, j).Value
End If
Next j
.Cells(i, iLastCol + 1).Value = _
Right(.Cells(i, iLastCol + 1).Value, Len(.Cells(i,
iLastCol
+ 1).Value) - 1)
Next i
End With

End Sub

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Katie" wrote in message
...
Hello All-

I've made my own round-a-bout way to do this procedure, but every time
I
have to change the array it takes a lot of work to correct. In this
range;
I3:BD10 there is either text or a "/". I need to concatenate each line
of
text and separate each value by a comma. For instance,

Red / Blue / = Red,
Blue
/ / Blue / =
Blue

What I've done is actually recorded myself going into the cells
afterwards
in a helper column and concatenating all of them and then using
find/replace
to remove extraneous commas and slashes. But, because of the
restrictions
to
how many cells you can concatenate I have to use 4 (!) helper columns.

Is there anyway to do this easier - I would want it to say-

"If any row in this array has text that is not a "/", concatenate it
and
separate it by a comma in a certain cell."

Concatenate Text Strings over Array
 

Call the UDF like this

=ConcatWithException(A1:D1)

In article ,
Katie wrote:

These are awesome- now I'm just having trouble finding exactly where to put
in my range and cell data.

Concatenate Text Strings over Array
 

I'm sorry to ask so many questions!

This is actually part of a 6 page macro- is there anyway to code it for
those specific cells that I can just place right into the existing code?

"JE McGimpsey" wrote:

Call the UDF like this

=ConcatWithException(A1:D1)

In article ,
Katie wrote:

These are awesome- now I'm just having trouble finding exactly where to put
in my range and cell data.

Concatenate Text Strings over Array
 

One way:

Dim sTest As String
sTest = ConcatWithException(Range("A1:D1"))


In article ,
Katie wrote:

I'm sorry to ask so many questions!

This is actually part of a 6 page macro- is there anyway to code it for
those specific cells that I can just place right into the existing code?

"JE McGimpsey" wrote:

Call the UDF like this

=ConcatWithException(A1:D1)