all the data in one cell and one column with one space.

[PRADEEPB270]

I have the data in an excel sheet. There are so many columns which exist my data.
I want to compile all data in a single cell with one space, say cell no A1.

Please refer my sheet, in which data exist from B6 to MV6 and now I want to arrange all these into one cell with one space i.e.in cell A1. How?

[Rudi]

Any of these methods will work:

1.
Select range B6:MV6 and copy
Open MS Word and paste the copied info
Select all the tab delimited numbers
Press CTRL+H (to open Find/Replace)
In the Find box type: ^t
In the replace box type: spacebar to add a single space (or type: ^s)
Click OK
Copy back to excel if needed

2.
In a blank cell, where you want to concatenate all the values type
=CONCATENATE(B6:MV6&" ")
Don’t press enter yet
Select the B6:MV6&" " portion and press F9
This replaces the B6:MV6&" " with its actual values
Now remove curly brackets { and } on both sides
Enter

3.
Use a basic UDF

Code: Select all

Function JoinData(DataRange As Range) As String
Dim rgC As Range, sVal As String
    For Each rgC In DataRange.Cells
        sVal = sVal & " " & Replace(rgC.Value, ",", "")
    Next rgC
    JoinData = Trim(Right(sVal, Len(sVal) - 1))
End Function