Need to remove the spaces or rather alt 0160 from cells

Topic

New Reply

I have data generated by SAS and everything that higher than 999 has a space not the sace created by a the space bar but rather by Alt 0160. Is there a function that will clean this up?

Reply | Quote

Replies

With your data selected, press Ctrl+H, click in the Find box, hold Alt key and type 0160 on the number keypad. Leave the Replace box blank and press Replace All

Reply | Quote

With your data selected, press Ctrl+H, click in the Find box, hold Alt key and type 0160 on the number keypad. Leave the Replace box blank and press Replace All

Thanks this what i have been doing but is there a function that can clean up the space?

Reply | Quote

If you mean a formula, then you can use SUBSTITUTE with CHAR(160)

Reply | Quote

Hi,

If you want to do something more sophisticated than a simple Find and Replace then check out the following:

http://excel.tips.net/T002947_Getting_Rid_of_Non-Printing_Characters_Intelligently.html

at this great site!

It does not mention Char 160 but this Function can be easily modified by a simple change to the macro.

Note this is a Function which, unlike a Find and Replace, will place your original data in another cell.

HTH
Peter Moran

Reply | Quote

Thank you so very much.

Reply | Quote