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.

Please Login or Register  to view this content.

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.