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

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

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

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" <KenCJohnson@gmail.com> wrote in message
news:1148191276.610827.126630@i39g2000cwa.googlegroups.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
>

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" <KenCJohnson@gmail.com> wrote in message
> news:1148191276.610827.126630@i39g2000cwa.googlegroups.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
> >
>
>
>

Hi Norman,

Thanks for that. Something new for me to learn.

Ken Johnson

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" <khoshravan@discussions.microsoft.com> wrote in message
news:70C01225-4610-4CA3-AB2B-0DFC321B4773@microsoft.com...
>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

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

Hi Rasoul,

Did you try David McRitchie's suggestions>

-
--
Regards,
Norman



"Khoshravan" <khoshravan@discussions.microsoft.com> wrote in message
news:06A21A94-6C1B-4927-9D5D-084A39781980@microsoft.com...
> 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

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" <khoshravan@discussions.microsoft.com> wrote in message
> news:06A21A94-6C1B-4927-9D5D-084A39781980@microsoft.com...
> > 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
>
>
>

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" <khoshravan@discussions.microsoft.com> wrote in message
> news:06A21A94-6C1B-4927-9D5D-084A39781980@microsoft.com...
> > 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
>
>
>