table shows lots of unexepected spaces when copies from Excel to Word

jackni

Board Regular
Joined
Feb 10, 2011
Messages
73
Hi, everyone

I'm writing a code to copy tables from EXCEL to WORD,and in WORD the code can adjust table's columnswidth.
I find an interesting thing in this process, that is when a table copies from EXCEL to WORD, it shows a lot of spaces in it's cells where the cell has number, but when the cell has characters, there will be no more spaces.

The unexepected spaces put a lot of trouble in adjusting table's columnwidth, in some cells(cells has number) the unexepected space is so much that make the cell has two or more rows, which makes the table looks terrible.

I know the replace function in WORD, but I have to locate the cells(has number),not the cells(has characters) first,and there are a lot of tables waiting to copy, which makes replace function looks not so good.

Can someone here please explain why table's spaces are much more in WORD than in EXCEL ,and how to make the spaces go away.

THANK YOU VERY MUCH!!!
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
The spaces are most likely due to whatever format you're using in Excel. Excel's Accounting format is especially liable to do this - with or without the $ symbols.
 
Upvote 0
The spaces are most likely due to whatever format you're using in Excel. Excel's Accounting format is especially liable to do this - with or without the $ symbols.
Yes, you are right. The cells which contain number is using Accounting format, this is exactly the tables need. When the table copies to Word, there will be much more spaces in Word's table,and the spaces will make cell more than one row,which makes the table looks terrible.
Is there a way to deal with this problem?
Thank you !!!
 
Last edited:
Upvote 0
The fairly obvious solution is to not use Excel's Accounting format; either that or widen the cells and/or adjust their left/right margins so they'll accommodate the data. Note, too, that reducing the column widths in Excel will reduce the amount of padding copied into Word.
 
Upvote 0
The fairly obvious solution is to not use Excel's Accounting format; either that or widen the cells and/or adjust their left/right margins so they'll accommodate the data. Note, too, that reducing the column widths in Excel will reduce the amount of padding copied into Word.
Thank you, Macropod.
I guess I'll have to use the replace function in WORD, cause due to the appearance request of the table, the number cells have to use Accounting format, and there is a columnwidth limit.
Macropod, any idea about why when using accounting format, table copies form Excel to Word, there will be lots of spaces showed up.
Thank you again, Macropod. :)
 
Upvote 0
Have you tried using the Currency format instead of the Accounting format?
Hi, Macropod,

I've tried the Currency format, it's same as Accounting format, and besides, the Accounting format is the table's asked format.
Any way, Thank you for your reply.
 
Upvote 0
I've tried the Currency format, it's same as Accounting format
They're actually quite different and give different results when pasted into Word. If you must use the Accounting format, you're left with the options I suggested earlier in this thread.
 
Upvote 0
They're actually quite different and give different results when pasted into Word. If you must use the Accounting format, you're left with the options I suggested earlier in this thread.
Macropod,
Thank you for your time and patience. I really appreciated.
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,291
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top