Len function returns bigger number
 

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

Len function returns bigger number
 

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

Len function returns bigger number
 

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


---
Regards,
Norman



"Khoshravan" wrote in message
...
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

Len function returns bigger number
 

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, Japan


"Khoshravan" wrote:

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

Len function returns bigger number
 

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, Japan


"Ken Johnson" wrote:

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

Len function returns bigger number
 

Hi Ken,

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


---
Regards,
Norman



"Ken Johnson" wrote in message
oups.com...
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

Len function returns bigger number
 

Hi Rasoul,

Did you try David McRitchie's suggestions

-
--
Regards,
Norman



"Khoshravan" wrote in message
...
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, Japan


"Khoshravan" wrote:

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

Len function returns bigger number
 

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


"Norman Jones" wrote:

Hi Ken,

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


---
Regards,
Norman



"Ken Johnson" wrote in message
oups.com...
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

Len function returns bigger number
 

Hi Norman,

Thanks for that. Something new for me to learn.

Ken Johnson

Len function returns bigger number
 

Give me few minutes more. Will finish soon and let you know if it solves my
problem.
--
Rasoul Khoshravan Azar
Civil Engineer, Osaka, Japan


"Norman Jones" wrote:

Hi Rasoul,

Did you try David McRitchie's suggestions

-
--
Regards,
Norman



"Khoshravan" wrote in message
...
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, Japan


"Khoshravan" wrote:

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

Len function returns bigger number
 

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


"Norman Jones" wrote:

Hi Rasoul,

Did you try David McRitchie's suggestions

-
--
Regards,
Norman



"Khoshravan" wrote in message
...
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, Japan


"Khoshravan" wrote:

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