trouble removing sticky spaces (2003)

Topic

New Reply

(Edited by HansV to make URL clickable – see Help 19)

Hi,

I have to start working with a new web -based extract that exports data into Excel 3.0 format. I can resave as the current version of Excel. The extract puts sticky spaces in blank cells and this is causing problems with a macro that I run. The person that wrote the macro is long gone and I don’t know how to get into it. I just click on the .xla file and a small toolbar pops up with a few options.

I found this from an earlier post :

Another thing to watch for is embedded “sticky spaces” (ASCII 160) . Some accounting programs will add “sticky spaces” to ensure that the numbers do not get reformatted. Excel knows to “trim” off regular spaces but treats “sticky-spaces” like regular text so many of the “conversion tricks” will not get rid of them.

Many times just find -0160 (hold alt key and type (no quotes): “0160” on numeric keypad) and replace with “nothing” [leave replace box “blank”] will get excel to automatically convert them.

:
I have tried doing a find and replace, but just can’t seem to get the ” Alt 0160″ into the find box.

If I filter the columns on blanks and overkey all the blanks with zeroes, then my macro works. This is not a practical solution for large amounts of data.

Does anyone have a method other than the one above of getting a sticky space into the FIND box?

At this site : http://www.ozgrid.com/forum/showthread/?t=31054%5B/url%5D

I found

This removes most unwanted characters: use it to clean the cells before populating:
Goes in a standard module:
VB: AutoLinked keywords will cause extra spaces before keywords. Extra spacing is NOT transferred when copy/pasting, but IS if the keyword uses “quotes”.
Function MEGACLEAN(Text As Variant)
‘ Function for removing characters
‘ Author: Parry
Dim NewVal As Variant
If IsMissing(Text) Then Exit Function
Application.Volatile True
NewVal = Trim(Text) ‘remove spaces
NewVal = Application.WorksheetFunction.Clean(NewVal) ‘remove most unwanted characters
NewVal = Application.WorksheetFunction.Substitute(NewVal, Chr(127), “”) ‘remove ASCII#127
NewVal = Application.WorksheetFunction.Substitute(NewVal, Chr(160), “”) ‘remove ASCII#160

MEGACLEAN = NewVal
End Function

would this work in Excel?

Thanks
capri

Reply | Quote

Replies

The function from Ozgrid is meant to be used as a worksheet function, that’s not what you want here.

Try running the macro from post 713,228.

Reply | Quote

Hans,

I don’t know how I can ever thank you for the macro. There are many problems with the new method of data extracts and what they contain, but this was the biggest one, impacting on so many calculations.

I was searching on sticky spaces rather than empty spaces.

As the Aussies say “Thanks Heaps”.

capri

Reply | Quote

Hi,

I was explaining to another person this morning about Han’s macro to remove sticky spaces. We also discussed another problem I have with a different extract. In the other extract, the first approx. 100 dates or number format properly. The remainder look like numbers or dates, but instead of aligning on the right hand side of the column, they align on the left and there is a small red triangle in the top corner. Once on this site someone suggested that if you had the cursor in the cell and hit F2, the cell would correct which it did. I could not block these columns and change the format. It occurred to me that perhaps there was a sticky space at the end of the number or date. I just got to work, tried Han’s macro and presto, it worked like a charm. So now I have 2 uses for that handy macro.

Thanks again Hans.

Reply | Quote

I’m glad it’s useful!

But the credit goes to others as well – I’m sure that other Loungers, such as Legare Coleman, contributed to the macro.

Reply | Quote

Hi,

I’ve run into a problem using the macro to get rid of sticky spaces. As you can see from the attached, it seemed to fix dates where the day was 1 to 12, but anything with a date 13 to 31 it did not fix. When I check the format, the ones that were fixed are formatted as dates and the others as general. Does any one have any ideas on what is happening or a possible solution?

Thanks
capri

Reply | Quote

Which date format do you use? The cells in the worksheet are formatted using European date format dd/mm/yyyy. If you use USA date format mm/dd/yyyy, confusion arises. You’d have to convert the values to USA date format before running the “sticky spaces” macro.

Reply | Quote

Hans,

My dates are generally dd/mm/yyyy. My PC is set up as English(Australian). When I click on them and check the format it is general. When I run the macro the attachment is what happens. If I put my cursor in a cell that didn’t convert and press F2, then it switches to be right aligned and format dd/mm/yyy, as do the dates 12 and below.

If I block the cells and change the format to date I still have some right and some left aligned. The left aligned ones will not group in pivot tables unless I go through each cell and hit F2. When working with 45,000 rows of data, that is not practical.

As I’m not the only person who works with data from this source, I think the only solution will be getting the people who run the database, to have it extract the data without the sticky spaces. I’ve been asking for that and so far they have fixed some of the date columns but not all.

It just puzzles me at to what is happening. I can’t understand why the dates would convert to the American format when I use the Australian format.

capri

Reply | Quote

It often happens that date and number values are imported as text values when a workbook is exported from another application.

A method that often works to force Excel to interpret a whole range of cells correctly (after removing the sticky spaces) is:
– Select the range of cells (they should not contain formulas).
– Press Alt+F11 to activate the Visual Basic Editor.
– Press Ctrl+G to activate the Immediate window.
– Type Selection.Value = Selection.Value and press Enter.

Reply | Quote