Easier way to concatenate a row?

[Allison]

Excel 2003 SP3
Windows XP SP2

I am trying to concatenate all cells in a row except the first cell. I can
do it this way, but was wondering if there were a "cleaner" or simpler way to
do it?

This is what I have currently:

=B2&D2&E2&F2&G2&H2&I2&J2&K2&L2&M2&N2&O2&P2&Q2&R2&S2&T2&U2&V2&W2&X2&Y2&Z2&AA2&AB2&AC2&AD2&AE2&AF2&AG2&AH2&AI2&AJ2&AK2&AL2&AM2&AN2&AO2&AP2&AQ2&AR2&AS2&AT2&AU2&AV2&AW2&AX2&AY2&AZ2

Thanks for any tips.

 

[akphidelt]

You can just put a formula underneath that row on cell C3 put

=B2&C2

Then copy and paste that formula all the way accross and then just use the
last of the formula as the concatenated value.

 

[Allison]

I have 26,000 rows. If I understand you correctly, I would need to make
52,000 rows? I don't think that would work. Thanks anyway.

 

[Ron Rosenfeld]

Excel 2003 SP3
Windows XP SP2

I am trying to concatenate all cells in a row except the first cell. I can
do it this way, but was wondering if there were a "cleaner" or simpler way to
do it?

This is what I have currently:

=B2&D2&E2&F2&G2&H2&I2&J2&K2&L2&M2&N2&O2&P2&Q2&R2&S2&T2&U2&V2&W2&X2&Y2&Z2&AA2&AB2&AC2&AD2&AE2&AF2&AG2&AH2&AI2&AJ2&AK2&AL2&AM2&AN2&AO2&AP2&AQ2&AR2&AS2&AT2&AU2&AV2&AW2&AX2&AY2&AZ2

Thanks for any tips.

A UDF would do it easily.

<alt-F11> opens the VBEditor.
Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the code below into the window that opens.

To use this, you could enter something like:

=SetString(0,B2:IV2)

====================================
Option Explicit
Function SetString(SpacesBetween As Integer, _
ParamArray rg() As Variant) As String

Dim c As Variant
Dim i As Long

For i = 0 To UBound(rg)
Select Case VarType(rg(i))
Case Is = vbArray + vbVariant
For Each c In rg(i)
SetString = SetString & Space(SpacesBetween) & Trim(c.Text)
Next
Case Is = vbString
SetString = SetString & Space(SpacesBetween) & Trim(rg(i))
End Select
Next i

SetString = Trim(SetString)

End Function
=======================================
--ron

 

[akphidelt]

You can also do like

=Concatenate(A1,B1,C1, etc)

Then copy and paste that all the way down the 26K rows

 

[Allison]

Thank you.

A UDF would do it easily.

<alt-F11> opens the VBEditor.
Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the code below into the window that opens.

To use this, you could enter something like:

=SetString(0,B2:IV2)

====================================
Option Explicit
Function SetString(SpacesBetween As Integer, _
ParamArray rg() As Variant) As String

Dim c As Variant
Dim i As Long

For i = 0 To UBound(rg)
Select Case VarType(rg(i))
Case Is = vbArray + vbVariant
For Each c In rg(i)
SetString = SetString & Space(SpacesBetween) & Trim(c.Text)
Next
Case Is = vbString
SetString = SetString & Space(SpacesBetween) & Trim(rg(i))
End Select
Next i

SetString = Trim(SetString)

End Function
=======================================
--ron

 

[Allison]

Thank you.

You can also do like

=Concatenate(A1,B1,C1, etc)

Then copy and paste that all the way down the 26K rows

 

[Allison]

This is even better than I thought it was when I said thanks earlier. It
works even if the cell contents are text or something else besides number or
named ranges. Thank you very much.

Allison

 

[Ron Rosenfeld]

This is even better than I thought it was when I said thanks earlier. It
works even if the cell contents are text or something else besides number or
named ranges. Thank you very much.

You're welcome. Thanks for the feedback.

By the way, if you haven't noticed, unlike the worksheet concatenate operator
or function, this UDF will retain any cell number or date formatting in the
output.
--ron