Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Joliette, Quebec, Canada
    Posts
    290
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Need to remove the spaces or rather alt 0160 from cells

    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?

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Joliette, Quebec, Canada
    Posts
    290
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by rory View Post
    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?

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    If you mean a formula, then you can use SUBSTITUTE with CHAR(160)
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts
    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_Gettin...lligently.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

  6. #6
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Joliette, Quebec, Canada
    Posts
    290
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Thank you

    Thank you so very much.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •