Merge the contents of selected cells

Dear all,

this is my first post in this great forum. let me start talking about what I need, I used to convert some statement from PDF to XLS format for my work purpose. sometimes I am facing a problem that some text in the statement will come in more than one row, actually I need it to come in one cell but it is coming in several rows that mean, if I want to sort or filter my data, I will lose some important data.

What do I want???

I want a macro to merge the contents of selected cells and put it in the first cell of the selected cells then clear the other cells.

I will use this macro for the following cases:

Case 1- assume we have this data in any sheet.

B1=How
C1=are
D1=you? ( all without any space )

after selecting the said cells and applying this macro, the result will be:

B1=How are you? ( one space only between the content of the selected cells )
C1=D1=empty

Case 2- assume we have this data in any sheet.

B1=How
B2=are
B3=you? ( all without any space )

after selecting the said cells and applying this macro, the result will be:

B1=How are you? ( one space only between the content of the selected cells )
B2=B3=empty

Case 3- assume we have this data in any sheet.

C1=How
D1=are
E1=you
C2=?
D2=What? ( all without any space )

after selecting the cells C1:D2 and applying this macro, the result will be:

C1=How are you ? What? ( one space only between the content of the selected cells )

D1=E1=C2=D2=E2=empty


I think every thing is clear up to now .

Now I am going to mention some specifications of this macro.

1- working using 2 keyboard shortcut, one to merge the contents with one space as cell contents separator; the other one to merge the contents without the space ( I will use this macro in the time of merging numbers for example )
2- as I mention above, I need this macro to work using key board shortcut, not using any button or form ...... because because such things will need more mouse movements.

I know that this work is too much and it need a time, but I will really appreciate any help regarding to this issue.

finally thanks in advance to all and have nice time.

Hello hbsqn,

Welcome to the Forum!

Add these 2 macros to your Workbook. You can then assign shortcut keys to each macro using the Macro Dialog. Press ALT+F8 to display it. Select the macro and then click the button Options... to assign the shortcut.

Sub CombineWithSpace()

  Dim A As Range
  Dim Cell As Range
  Dim FirstCell As Range
  Dim I As Long
  
    For Each A In Selection.Areas
      If FirstCell Is Nothing Then Set FirstCell = A.Cells(1, 1)
      For I = 1 To A.Cells.Count
        If A.Item(I) <> FirstCell Then
           FirstCell = FirstCell & " " & A.Item(I)
           A.Item(I) = ""
        End If
      Next I
    Next A
    
End Sub

Sub CombineWithNoSpace()

  Dim A As Range
  Dim Cell As Range
  Dim FirstCell As Range
  Dim I As Long
  
    For Each A In Selection.Areas
      If FirstCell Is Nothing Then Set FirstCell = A.Cells(1, 1)
      For I = 1 To A.Cells.Count
        If A.Item(I) <> FirstCell Then
           FirstCell = FirstCell & A.Item(I)
           A.Item(I) = ""
        End If
      Next I
    Next A

End Sub

Adding the Macro
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time.
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.