| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Len function returns bigger number
Hi Norman,
Thanks for that. Something new for me to learn. Ken Johnson |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
| Displays the number in text. (One thousand two hundred thirty four | Excel Worksheet Functions | |||
| Force function to show positive or negative number? | Excel Worksheet Functions | |||
| Function that Returns address of that cell? | Excel Worksheet Functions | |||
| PERMUT function | Excel Worksheet Functions | |||
Linear Mode
