1

I am using Office 2003 under windows XP Pro

I copy and paste large lists from ms access 2003 database into excel daily so I can do various functions to the lists. The one problem I am having now is figuring out how to remove the spacial spaces (I thought it was called white space but it seems not the case) after the numbers or text. 

example:

[1234     ]
[1234     ]
[1234     ]
[1234     ]
[34     ]
[12ABC     ]

(please note, the example above will work if you copy/paste. I was just giving example, visually.)

In excel, =trim() does not remove them. Also, (Search/Replace " " and leave empty) does not remove them. I have also tried a trick I use in other situation, copy/paste into notepad and then search/replace, did not remove them. That is why I called the term, special. But there is probably an actual term--I wouldn't mind knowing what its called, officially.

I am not looking for a custom =function() in excel to run.

This would not be practical to use in every day copy/paste since these are going directly into someone elses excel reports. I am adding my info to it and it gets processed further by others who are not excel savvy.

brettdj
  • 54,857
  • 16
  • 114
  • 177
johnm2
  • 143
  • 3
  • 8
  • Possible duplicate of http://stackoverflow.com/questions/16060056/how-to-remove-a-single-leading-space-in-the-numeric-column-in-excel-2013/ – user2140261 Apr 25 '13 at 16:07
  • Would a VBA Code help? – Siddharth Rout Apr 25 '13 at 16:09
  • i searched google (advanced search page) and viewed many suggestions, (all were the same) but not resolved. the issue is spacial. i don't know the correct term for this..that's why i can't find. – johnm2 Apr 25 '13 at 16:14
  • Ignore my last comment. Just notice that you are not looking for a VBA option. – Siddharth Rout Apr 25 '13 at 16:16
  • Siddharth, no, it would not, because i am constantly copy/pasting between the two and things go rather quickly in my work. i program in delphi, i could write something as well, but is not practical. i need this to be an excel answer. i believe the problem is a special "code" character to search for. but i don't know what code ms access is placing in some of the data i copy from. if i knew then i could search/replace by that. – johnm2 Apr 25 '13 at 16:17
  • 2
    Did you look at the link i Posted you have non breaking spaces/CHAR(160) this is very common with data imports. Please review my answer To this question: http://stackoverflow.com/questions/16060056/how-to-remove-a-single-leading-space-in-the-numeric-column-in-excel-2013/16060772#16060772 or for how to do `Find and Replace` refer to Ole Henrik Skogstrøm's answer to the same question and to learn more you can look at this article: http://office.microsoft.com/en-001/excel-help/remove-spaces-from-the-beginning-and-end-of-a-cell-HP003056131.aspx – user2140261 Apr 25 '13 at 16:17
  • appolgies..i'm at work..and interrupted a lot. – johnm2 Apr 25 '13 at 16:22
  • 1
    I appologize Ole Henrik Skogstrøm's answer was not on `Replace` but to remove in place. To `Replace` search for `ALT+0160` – user2140261 Apr 25 '13 at 16:24
  • 1
    i resolved it before i read user2140261 response. both methods work. but alt+0160 is quicker, thank you. but in case anyone else needs to know.. in excel, bring up search/replace, press and hold the ALT key and while in the search box, enter 0160 (in numeric keypad, quickly ) and then release the ALT key, then leave the replace box empty and hit ok button. – johnm2 Apr 25 '13 at 16:42
  • Duplicate of [How to remove leading or trailing spaces in an entire column of excel worksheet in Excel2010](http://stackoverflow.com/questions/9578397/how-to-remove-leading-or-trailing-spaces-in-an-entire-column-of-excel-worksheet) – brettdj Apr 28 '13 at 05:14

1 Answers1

0

Have you tried the =clean() function? It removes many unusual characters.

Also you could use the =substitute() function if you know which specific ascii characters are being naughty. They likely are #160's as suggested in the comments.

=SUBSTITUTE(A1,char(160),"")

would work for that instance.

Hope that helps

Alan Waage
  • 603
  • 4
  • 12