trim and clean not working!

[Larry]

I have pasted text from another app.

It looks like it has a space at the beginning.

When typing text in a cell a space must be put in

at the beginning to make it align with the text pasted in.

I have used both CLEAN and TRIM to no avail it just trims the text that was
typed and leaves the pasted text with the space at beginning.

I have also tried pasting it into wordpad and notepad and pasting it back to
excel and the space is still there. Any Ideas?

 

[Domenic]

Hi Larry,

If you want to get rid of only the first space, and assuming that your
text is in Column A, then try something like this,

enter the following formula in a column and copy down as far as you need
to:

=RIGHT(A1,LEN(A1)-1)

select the new column
edit > copy > edit > paste special > values
delete the old column

Hope this helps!

 

[Larry]

thanks, domenic

 

[Domenic]

thanks, domenic

You're welcome, Larry!

 

[David McRitchie]

Hi Larry, and Domenic,
While it may have worked out for you this time, I think there are
less risky ways that you can fix your data without removing something
that might be good data on another occasion.

The character is probably a char(160) which is the non-breaking
space character (in HTML   ). You could manually
change them from: Alt+0160 to: (space)
and them simply TRIM them without using LEFT or RIGHT.

But I would suggest doing everything in one shot with a macro to
change data in place based on your selection and be done with
the problem.
TrimALL macro (#trimall)
Rearranging Data in Columns
http://www.mvps.org/dmcritchie/excel/join.htm

 

[Domenic]

Hi David,

I can definitely see how it would be less risky in using the method you
outlined. I appreciate you pointed that out.

By the way, if you know off hand what the Macintosh equivalent of
Alt+0160 is, I'd appreciate it if you could pass it along.

Thanks very much!

 

[Harlan Grove]

If you want to get rid of only the first space, and assuming that your
text is in Column A, then try something like this, ....
=RIGHT(A1,LEN(A1)-1)

....

Alternatives each using a single function call,

=MID(A1,2,1024)

=REPLACE(A1,1,1,"")

 

[Domenic]

Yes, definitely more efficient!

Thanks Harlan!

...

Alternatives each using a single function call,

=MID(A1,2,1024)

=REPLACE(A1,1,1,"")