#  Other Applications & Softwares  > Word Formatting & General >  >  Remove spaces in data copied from Excel

## antonf

I have to copy certain columns from an Excel sheet and paste it in a Word table.

That's a doddle, but for some reason after I've pasted the columns all the values have a number of spaces in front of them.

How do I prevent this from happening.  Deleting the multiple spaces is easy, but the last one is not so easy. Please note that I am forced to use spaces as the thousands separator, so I cannot delete all the spaces!

----------


## Richard Buttrey

Hi,

Have you tried the =TRIM() function to remove preceding or trailing spaces?

HTH

----------


## antonf

Excuse my ignorance, but how do I get to use an Excel function in Word?

----------


## contaminated

Try next
1 Open your document\
2 Press Ctrl+H
3 Click More > Special > White Space
4 Leave "Replace with" field bland or type there smth.
5 Press Replace All button

HTH

----------


## antonf

Thanks HTH, it works fine but then deletes the separator between the thousands as well.

I gave up on the issue - selected the table and selected and replaced all "double spaces" with single spaces, which left me with preceding spaces as well.  Juggling with the column widths assisted in "hiding" the impact of those spaces.

----------


## Richard Buttrey

Hi,

I had assumed that the spaces were present in the Excel sheet and copied over, hence the advice to get rid of them with the =TRIM() before copying and pasting.

Rgds

----------


## artthai

Experienced the same problem. These extra spaces are annoying!!!!!

*Solution*
My solution was to change the cell formatting to something simple like "Number" and not a custom like "_-* #,##0_-;-* #,##0_-;_-* "-"??_-;_-@_-".

This has worked for me... so good luck!

----------


## Anthony Gill

Thanks artthai, I have frequently encountered this problem when preparing PowerPoint presentations, and I do like to be able to modify the charts within PowerPoint rather than paste objects into PowerPoint. I do use custom number formats all of the time, and I didn't realise that this could be the trigger to my problem with spaces appearing before numbers when being pasted into PowerPoint. Changing the number format to "number" as you suggested worked fist time, which was a rather nice feeling!
Thanks.

----------


## newdoverman

In word you can use the Search and Replace. In the Replace field enter this " {2,15}"  don't enter the " marks so what you enter is a space, curly brackets, 2 comma 15.  This will search for at least 2 spaces together up to 15 spaces together.

Leave the Replace With field empty.

Select WILD CARDS then replace all.

If you want to restrict the Search and Replace to a certain area of text, select that text first then do the Search and Replace.

----------


## 1tane1

What does wild cards do?

----------


## newdoverman

The wild cards allow the search and replace to accept multiple items in the find. In this case, it will find 2 spaces together up to 15 spaces together.

----------


## 1tane1

Ty for explanation.

Also if you want to delete zeros in such a case (for example:  " 178 000 000") you can replace " 0" by "#" and then remove all spaces and then put normal spaces back by replacing "#" with " 0". Little tricks depend on situations.

----------


## newdoverman

The use of the wild card instead of using a substitution allows you to target 2 to 15 spaces for replacement while leaving single spaces between words untouched.

----------


## arlu1201

1tane1,

For future reference, please do not hijack other users' posts.  Instead, create your own thread and you can link back to this one.

----------


## syahrilazhar

What I did is simply copied the exact spaces and use the function Find and Replace

Paste the copied spaces in the Find What: field and leave blank in the Replace With: field.

Select all content in my word document and press Replace All button. It will rectified for you.

Regards 
Syahril
Malaysia

----------

