Deleting leading and trailing spaces from a number

**maacmaac** · 03-31-2009, 04:28 PM

I am using the following code to import an excel file into an existing sheet. The problem I am having is the way some of the cells are being imported. Some of the cells have spaces before and after the number. I need to delete the leading and trailing spaces for each cell affected. Is there a code I can incorporate to do this? BTW…the columns effected are columns F & G. Thanks

Please Login or Register  to view this content.

**shg** · 03-31-2009, 04:51 PM

The Trim function removes leading and trailing spaces. You'd need to loop through the cells to apply it.

**maacmaac** · 03-31-2009, 05:30 PM

I tried using the Trim Function in the code but no luck. I even tried to run the function on it's own for each cell in columns F, G, & H and that did not work either. I am stumped. I attached both files for review if needed. The file I am importing from has hyperlinks to each number but I am only pasting values. I'm not sure if that is part of the problem with the trim function not working. Thanks.

**shg** · 03-31-2009, 05:32 PM

Did you check to see if the leading/trailing characters are indeed blanks, and not, for example, non-breaking spaces?

**shg** · 03-31-2009, 07:55 PM

It is indeed a non-breaking space.

In VBA, try

Please Login or Register  to view this content.

**maacmaac** · 03-31-2009, 10:15 PM

Shg,

Thank you for information. The VBA code is working as expected. One more question if you don't mind? How are you able to tell the difference between a "blank" and a "non-breaking space"? And what is the difference? Thanks again.

**shg** · 03-31-2009, 10:26 PM

A blank is character code 32. A non-breaking space is character code 160.

I have an add-in that shows the ANSI character codes in a cell, and am happy to upload it if you're comfortable with add-ins.

Otherwise, you can do =CODE(Mid(A1, x, 1)) to see the character code for any particular character x.

**maacmaac** · 03-31-2009, 10:35 PM

Thanks for the info. And yes, I would have an interest in the add-in. If you can upload it, that would be great. Thanks.

**shg** · 03-31-2009, 11:25 PM

I said I had this as an add-in, but I actually have it in Personal.xls. It comprises a form and a code module; can you sort it from there?

I shorttcut the sub as Ctrl+Shift+B, mentally attached as Conveniently Show Binary.

Deleting leading and trailing spaces from a number

LinkBack

Thread Tools

Rate This Thread

Display

Deleting leading and trailing spaces from a number

Re: Deleting leading and trailing spaces from a number

Re: Deleting leading and trailing spaces from a number

Re: Deleting leading and trailing spaces from a number

Re: Deleting leading and trailing spaces from a number

Re: Deleting leading and trailing spaces from a number

Re: Deleting leading and trailing spaces from a number

Re: Deleting leading and trailing spaces from a number

Re: Deleting leading and trailing spaces from a number

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions