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

[jackni]

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!!!

 

[Macropod]

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.

 

[jackni]

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 !!!

 

[Macropod]

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.

 

[jackni]

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.

 

[Macropod]

Have you tried using the Currency format instead of the Accounting format?

 

[jackni]

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.

 

[Macropod]

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.

 

[jackni]

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.