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