software

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
--
Rasoul Khoshravan Azar
Civil Engineer, Osaka, Japan

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 JohnsonHi Rasoul,

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

See David McRitchie's TrimAll page at:

www.mvps.org/dmcritchie/excel/join.htm#trimall---
Regards,
Norman
quot;Khoshravanquot; gt; wrote in message
...
gt;I import PipeID from an engineering software about pipe analysis (EPNET).
gt; and put them in column A:A, like A1:KP51.
gt; When I perform LEN functionin this cell, LEN(A1), it returns 17. I suppose
gt; it should return 4
gt; I cant understand why.
gt; I have these pipID (like KP51) made by excel as well.
gt; When I want to perform countif, on these PipeID's, it doesn't work. I
gt; think
gt; I have to change the format of this column
gt; --
gt; Rasoul Khoshravan Azar
gt; Civil Engineer, Osaka, Japan
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?
--
Rasoul Khoshravan Azar
Civil Engineer, Osaka, Japanquot;Khoshravanquot; wrote:

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

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
--
Rasoul Khoshravan Azar
Civil Engineer, Osaka, Japanquot;Ken Johnsonquot; wrote:

gt; Hi Rasoul,
gt;
gt; If there are extra spaces TRIM worksheet function will remove them.
gt; If they are nonprintable characters then CLEAN worksheet function will
gt; get rid of them.
gt;
gt; Ken Johnson
gt;
gt;

Hi Ken,

If your suggestions do not resolve Rasoul's problem, it is possible that his
data includes non breaking space characters.---
Regards,
Norman
quot;Ken Johnsonquot; gt; wrote in message oups.com...
gt; Hi Rasoul,
gt;
gt; If there are extra spaces TRIM worksheet function will remove them.
gt; If they are nonprintable characters then CLEAN worksheet function will
gt; get rid of them.
gt;
gt; Ken Johnson
gt;
Hi Rasoul,

Did you try David McRitchie's suggestionsgt;

-
--
Regards,
Norman
quot;Khoshravanquot; gt; wrote in message
...
gt; are In addition to my previous issue, I think there some spaces entered to
gt; end of text. I think I have to look for a way to delete these extra
gt; spaces.
gt; Is there any way?
gt; --
gt; Rasoul Khoshravan Azar
gt; Civil Engineer, Osaka, Japan
gt;
gt;
gt; quot;Khoshravanquot; wrote:
gt;
gt;gt; I import PipeID from an engineering software about pipe analysis (EPNET).
gt;gt; and put them in column A:A, like A1:KP51.
gt;gt; When I perform LEN functionin this cell, LEN(A1), it returns 17. I
gt;gt; suppose
gt;gt; it should return 4
gt;gt; I cant understand why.
gt;gt; I have these pipID (like KP51) made by excel as well.
gt;gt; When I want to perform countif, on these PipeID's, it doesn't work. I
gt;gt; think
gt;gt; I have to change the format of this column
gt;gt; --
gt;gt; Rasoul Khoshravan Azar
gt;gt; Civil Engineer, Osaka, Japan
Hi Norman
I am reading David TrimAll article hopefully to solve the issue.
New Findings:
PipeID (quot; KP51quot;) 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.
--
Rasoul Khoshravan Azar
Civil Engineer, Osaka, Japanquot;Norman Jonesquot; wrote:

gt; Hi Ken,
gt;
gt; If your suggestions do not resolve Rasoul's problem, it is possible that his
gt; data includes non breaking space characters.
gt;
gt;
gt; ---
gt; Regards,
gt; Norman
gt;
gt;
gt;
gt; quot;Ken Johnsonquot; gt; wrote in message
gt; oups.com...
gt; gt; Hi Rasoul,
gt; gt;
gt; gt; If there are extra spaces TRIM worksheet function will remove them.
gt; gt; If they are nonprintable characters then CLEAN worksheet function will
gt; gt; get rid of them.
gt; gt;
gt; gt; Ken Johnson
gt; gt;
gt;
gt;
gt;

Hi Norman,

Thanks for that. Something new for me to learn.

Ken JohnsonGive me few minutes more. Will finish soon and let you know if it solves my
problem.
--
Rasoul Khoshravan Azar
Civil Engineer, Osaka, Japanquot;Norman Jonesquot; wrote:

gt; Hi Rasoul,
gt;
gt; Did you try David McRitchie's suggestionsgt;
gt;
gt; -
gt; --
gt; Regards,
gt; Norman
gt;
gt;
gt;
gt; quot;Khoshravanquot; gt; wrote in message
gt; ...
gt; gt; are In addition to my previous issue, I think there some spaces entered to
gt; gt; end of text. I think I have to look for a way to delete these extra
gt; gt; spaces.
gt; gt; Is there any way?
gt; gt; --
gt; gt; Rasoul Khoshravan Azar
gt; gt; Civil Engineer, Osaka, Japan
gt; gt;
gt; gt;
gt; gt; quot;Khoshravanquot; wrote:
gt; gt;
gt; gt;gt; I import PipeID from an engineering software about pipe analysis (EPNET).
gt; gt;gt; and put them in column A:A, like A1:KP51.
gt; gt;gt; When I perform LEN functionin this cell, LEN(A1), it returns 17. I
gt; gt;gt; suppose
gt; gt;gt; it should return 4
gt; gt;gt; I cant understand why.
gt; gt;gt; I have these pipID (like KP51) made by excel as well.
gt; gt;gt; When I want to perform countif, on these PipeID's, it doesn't work. I
gt; gt;gt; think
gt; gt;gt; I have to change the format of this column
gt; gt;gt; --
gt; gt;gt; Rasoul Khoshravan Azar
gt; gt;gt; Civil Engineer, Osaka, Japan
gt;
gt;
gt;