How to remove a space in the front

Hi gurp99,
Your data has that darn char(160) in front and in the rear of each string. It looks like a space but isn't
In B1 and copy down this formula to remove all of them.

Please Login or Register  to view this content.

Copy and then Paste (Values Only) over the old text to have clean data.

hope that helps.

gurp99,

They are ASCII 160 characters (not space characters).

To remove them:

Click on the first cell in column A.
Then press the F2 key
Copy the leading character

Then click on the column containing the data that contains the ASCII 160 characters.

Then click and hold down the ALT key and press the E key

Then presss the F key

On the Find tab, In the Find what: box paste in the ASCII 160 character that you copied.

Click on the Replace tab

And then click on the Replace All button, the click on the Close button

or just in find
hold down alt key type 0160 on the numeric keypad
replace all

Hi guys, how does one recognise that the space is char 160 and not an ordinary space? what distinguishing features does it have? thanks alot.

just use =CODE(A1) that will give you the character code of the first char or code(right(a1)) for last or use mid or delete some and leave it on its own,

Thanks martin

@Blake,

To solve this problem here is how it went. I think this will appease arthurbr.

Next to A1 in B1 I used the formula =Trim(A1)
Then in C1 I used the formula =Len(A1)
and in D1 I used the formula =Len(B1)
The Lenght of both strings were the same after the Trim function.
This meant the spaces in front weren't spaces as Trim would have removed them.

To determine what the first character was I used this formula: =CODE(LEFT(A1,1))
which returned the number of 160. This then lead to the above answer(s).

I hope this fully explains this problem and how it lead to the answer.

Thanks alot Marv for your comprehensive response and for taking the time to do so.

Hi Art - thanks for the heads up.

while were at it!
left(a1)=left(a1,1) and code(a1)=code(left(a1)) both
code and left on their own look to the left most character without any further arguments