Merging text in a single cell from a range

[rubic]

How can I merge into a single cell from a range of selected cells that contain text. If the range of cells do have empty cells it should be smart enough to skip over or not to create an additional space. Any idea how can this be done? Macro or formula?

 

[rubic]

I have tried using concatenate function but I need to create a space in between text from each cell so the text will not join together a single meaningless word.

 

[MickG]

Hi, If you select your cells by holding Down "Ctrl" , to select non contiguious cell/ranges, then run the code it will put the complete string" in "A1".

Dim cl As Range, Txt As String
For Each cl In Selection 
If cl <> "" Then
Txt = Txt & cl & Chr(32)
End If
Next cl
Range("A1") = Txt

Regards Mick

 

[Gary McMaster]

Strange, I needed the same thing yesterday.

I used the code below in the "right click event". I could drag over the range with the LMB. Right click combines to first cell in range and clears the rest.

It delimits the merged text with a semi-colon and a space.

Hope it helps.

Gary

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

Dim oRange As Range
Dim oCell As Range
Dim sTemp As String

Set oRange = Application.Selection

sTemp = ""
For Each oCell In oRange
If oCell.Value <> "" Then
sTemp = sTemp & oCell.Value & "; "
End If
Next

sTemp = Left(sTemp, (Len(sTemp) - 2))

oRange.Clear

oRange.Cells(1).Value = sTemp


End Sub

 

[rootbeer001]

i do this when working with first and last names in seperate columns:

=concatenate(A1," ",B1)

putting a space in quotes will place it between the two different text strings.

 

[rubic]

Thank guys for your suggestion and help. I will try out to see which proposal suit my needs best.

 

[rubic]

Gary Mcmaster,
About your codes. If I need the merged text to be in the active cell rather than cell A1 how will the codes be like?

 

[rubic]

or rather to be on the first cell of the selected range of cells to merge the text.

 

[Gary McMaster]

or rather to be on the first cell of the selected range of cells to merge the text.

That's what it does as I showed it. Select a range of cells, right click, and it will merge the text from the selected range into the first cell of that selected range.

At the end of this line:

sTemp = sTemp & oCell.Value & "; "

The "; " gets added between each piece of merged text. Change it to whatever you want.

Gary

 

[rubic]

Gary,

Sorry I got the wrong guy regarding my question before. About youryour suggested codes. I put these codes is in the sheet itself. Right clicking did not seem to do anything. Did I put the codes in the wrong location?