Indent from paste remains after TRIM

[HuskerBronco]

If I paste data from an external source into my spreadsheet, and data from that external source is
sporadically formatted so that some cells are justified, and others are Left Indent(1)...why doesn't my
TRIM function remove the space to the left of data that is Left Indented??

Consider this sample paste from my list:
(Hypothetically, this list appears in A1:A9)

MICHIGAN
*PURDUE
*SYRACUSE
MISSOURI
*LOUISVILLE
*TENNESSEE U
CLEMSON
*FLORIDA
*IOWA

To repair this list so that the data no longer conflicts with formulas in my existing workbook,
I have entered this formula into B1, and copied it down to B9 :

=TRIM(PROPER(A1))

Data in B1:B9 is now in PROPER case, but the Left Indent remains.
I have even formatted cells B1:B9 to Justify data............Didn't work.

Am I doing something wrong?......... ...... ......

[Special-K]

Use a Helper column in column c

=CODE(A1)

and copy it down the column

This returns the ASCII value of the first character in each row. If it dont say 32 then it aint a space as you originally thought

[Bryan Hessey]

If I paste data from an external source into my spreadsheet, and data from that external source is
sporadically formatted so that some cells are justified, and others are Left Indent(1)...why doesn't my
TRIM function remove the space to the left of data that is Left Indented??

Consider this sample paste from my list:
(Hypothetically, this list appears in A1:A9)

MICHIGAN
*PURDUE
*SYRACUSE
MISSOURI
*LOUISVILLE
*TENNESSEE U
CLEMSON
*FLORIDA
*IOWA

To repair this list so that the data no longer conflicts with formulas in my existing workbook,
I have entered this formula into B1, and copied it down to B9 :

=TRIM(PROPER(A1))

Data in B1:B9 is now in PROPER case, but the Left Indent remains.
I have even formatted cells B1:B9 to Justify data............Didn't work.

Am I doing something wrong?......... ...... ......

The Paste copies format as well as data, you can Copy and Paste Special = Values to remove the format.

An Indent is not a space, thus the Trim will not remove it.

---

[HuskerBronco]

Special-K...I did exactly that, and discovered that it was ASCII 160 causing my problem. As you are probably aware, that is the code that represents a "space", or specifically, the non breaking space character "(   )" in HTML code.

While viewing previous posts, I came across the solution to this menacing problem. Last year, User Dave Peterson posted a reply to a woman that steered me directly to a web page explaining the problem in detail, as well as providing the Macro that I needed to clean up the data. The link is below, and I would highly recommend that anyone viewing this post visit the web page and read up on this, if you aren't familiar with it already.

Here is the link:
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

Thanks Dave, for answering my question.

I really appreciate YOUR quick response, as well, Special-K.

But, I gotta hand it to Dave for answering my question a year and a half before I even posted it..... ...LOL...