How to copy Word table into Excel without splitting cells into multiple rows?

Honestly we work a lot on Word, Excel and PowerPoint, to an extent that we never thought while we were in school or university that this is what we would end up doing ultimately.

This makes it immensely important that we know some nifty tricks to quickly work our way out, on to the next job. This has been a long pending work for me and this time I had to look for a solution (because the data size was huge and i couldn't do it one by one).

And here is how we do it:

In Word:

1. Select your entire table in Word.
2. Open the "Find and Replace" dialog (or Ctrl+H).
3. In the "Find what" field, enter ^l (Shift+6 and the first letter of word lion).
4. Now when you click on Find, it will search for all the line breaks. If there are no line breaks then look for paragraph breaks.
5. You may select paragraph breaks by entering ^p (Shift+6 and the first letter of word parrot).
6. In the "Replace with" field, enter ^v (Shift+6 and the first letter of word Venice).
7. This is a shortcut for the paragraph symbol ¶. This is also known as a "pilcrow".
8. Click "Replace All".

Now copy the table data from your Word file.

In Excel:

1. Paste the copied table in Excel.
2. With the data still selected, open "Find and Replace" (or Ctrl+H).
3. In the "Find what" field, enter the following Alt code: Alt+0182. This is the Alt code for the pilcrow symbol. To enter an Alt code, ensure the numeric keypad is available (i.e., Num Lock on).
4. In the Replace field, enter the Alt code: Alt+0010 (this is for the line break). Nothing appears to change but it works.
5. Click "Replace All".

And that's it. All the rows copied as is without splitting of content into multiple rows.

Hope this helps! Until next time.

Excel VBA script to open a file system folder

Excel VB commands to Convert Numbers into Currency

In the below example we are converting a Number to Currency and we are using '$' symbol for display.


We can achieve this using alternate commands like the ones in the below example. However, the above command is a short-hand. Quite handy, ain't it?

Output after converting to INR

We can also change the currency. Suppose we were to display INR (Indian Rupees)




To learn about formatting numbers in Office Excel (the ones like #.##, #,###.00 etc. that you see in the above examples), I would recommend you to refer Excel Help and search for the topic "Create or delete a custom number format"