Len function returns bigger number

[Guest]

I import PipeID from an engineering software about pipe analysis (EPNET).
and put them in column A:A, like A1:KP51.
When I perform LEN functionin this cell, LEN(A1), it returns 17. I suppose
it should return 4
I cant understand why.
I have these pipID (like KP51) made by excel as well.
When I want to perform countif, on these PipeID's, it doesn't work. I think
I have to change the format of this column

 

[Ken Johnson]

Hi Rasoul,

If there are extra spaces TRIM worksheet function will remove them.
If they are nonprintable characters then CLEAN worksheet function will
get rid of them.

Ken Johnson

 

[Norman Jones]

Hi Rasoul,

The imported data probably contains spaces or, possibly non breaking space
characters ( CHR(160)).

See David McRitchie's TrimAll page at:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

 

[Guest]

are In addition to my previous issue, I think there some spaces entered to
end of text. I think I have to look for a way to delete these extra spaces.
Is there any way?

 

[Guest]

Hi Ken
Thanks for your comment. I performed both TRIM and Clean but both functions
still bring 17 for the result of LEN function for LEV(KP51). Very strange

 

[Norman Jones]

Hi Ken,

If your suggestions do not resolve Rasoul's problem, it is possible that his
data includes non breaking space characters.

 

[Norman Jones]

Hi Rasoul,

Did you try David McRitchie's suggestions>

-

 

[Guest]

Hi Norman
I am reading David TrimAll article hopefully to solve the issue.
New Findings:
PipeID (" KP51") has one space in the beginning
I did following two actoins. Result are interesting
Left(A1;5) and LEN for this returns: 17
MID(A1;2;4) and LEN for this returns: 17
So probably this is Char 160 which Norman mentions and according to David,
they will not be cleaned or Deleted by TRIM and CLEAN.

 

[Ken Johnson]

Hi Norman,

Thanks for that. Something new for me to learn.

Ken Johnson

 

[Guest]

Give me few minutes more. Will finish soon and let you know if it solves my
problem.

 

[Guest]

Hi Norman. Yes it works. Very nice solution. In fact very fantastic.
David's site sounds like the Bible for Excel Users.
Thanks for your comments and solution