Annoying leading space

[Jeffrey Green]

I have 55,000 rows with a column containing the word "Ford", but sometimes there is a leading "space" so it
appears as " Ford". Very annoying . . .

It isn't a space and i tried CLEAN() and TRIM() with no luck . . .

Thoughts?

Thanks

 

[VoG]

Possibly CHAR(160). Try selecting the data then running the TrimAll macro http://dmcritchie.mvps.org/excel/join.htm#trimall

 

[Dannyh1]

hate it when this happens, could maybe do a quick & dirty formula.

=MID(A1,FIND("Ford",A1),LEN(A1)-(FIND("Ford",A1)-1))

 

[Jeffrey Green]

No luck . . . sample below . . . note the space in front of TRUCK (it isn't as noticeable here). . . .It is happening more than one column . .

SEA</SPAN>

SEA</SPAN>

SEA</SPAN>

SEA</SPAN>

TRUCK </SPAN>

TRUCK </SPAN>

TRUCK </SPAN>

<TBODY>
</TBODY>

 

[Jeffrey Green]

sorry danny, no luck . . . When I do LEN(), Excel returns the correct number of characters)

 

[Dannyh1]

Hmm - what about the alignment - maybe there is no space but an indent?

 

[Jeffrey Green]

mmm, not sure . . .I fixed it the poor man's way . . . .I cleared all data, than typed it the correct word and copied it down . . . . .
Thanks though, I will look for that if it happens again . . . .

Closed . . . . with thanks

 

[Dannyh1]

It can't have been that then as that would have been cell format so same would happened. How annoying then!

 

[Jeffrey Green]

Ok, I am an idiot . . . somehow all of my text columns got formatted as ACCOUNTING!!!!

Sheeeeesh . . . .

Thanks