Reverse text to columns

  • Thread starter Thread starter Rick Bedard
  • Start date Start date
R

Rick Bedard

Is there any way to combine text from multiple columns into one column? Sort
of like the reverse of or undoing the "text to columns" feature.

I know I cn use the "&" symbol in a formula e.g. =A1&B1&C1 but this
leaves a formula in the cell so if I delete the original cells it no longer
works.

I'd like to simply merge columns that split text into multiple columns back
into one column.

Thanks!!
 
But you can simply copy>paste special>values in place
to overwrite the "&" formulas to convert to values?
 
One way, using a macro:

Public Sub ColumnsToText(Optional rRng As Range, _
Optional sDelim As String = "")
Dim vTxtArr As Variant
Dim nTop As Long
Dim i As Long
Dim j As Integer
If rRng Is Nothing Then Set rRng = Selection
Set rRng = Intersect(rRng, rRng.Parent.UsedRange)
vTxtArr = rRng.Value
nTop = UBound(vTxtArr, 1)
For i = 1 To nTop
For j = 2 To UBound(vTxtArr, 2)
vTxtArr(i, 1) = vTxtArr(i, 1) & sDelim & vTxtArr(i, j)
Next j
Next i
ReDim Preserve vTxtArr(1 To nTop, 1 To 1)
rRng.Resize(, 1).Value = vTxtArr
End Sub
 
Back
Top