Getting #VALUE! for formula involving cells that have more than 255 characters in them

[learningstatistics]

I have around 34,000 observation and I am subtracting dates. So, the formula works well for all observations except 18 of them. I noticed that if a cell has 255 characters, a formula on it works fine. Once the cell has 256 characters, it gives me a #VALUE!

Here's how the data is like:

14:50:56,14:51:10,14:51:11,20:28:38,20:28:38,20:28:38,20:28:38 <colgroup><col width="145" style="width:109pt"> </colgroup><tbody> </tbody>	=IF(LEN(TEXT(E1249,"hh:mm:ss"))>8,RIGHT(E1249,FIND(",",E1249)-1),TEXT(E1249,"hh:mm:ss"))	Works fine
14:51:11 <colgroup><col width="145" style="width:109pt"> </colgroup><tbody> </tbody>		Works fine
Cell that has more than 256 characters and above starting with 13:23:34,15:23:34,etc		Gives error #VALUE!

<tbody>
</tbody>

How is it possible that the formula works for around 33,982 observations and fails for only 18? Does the number of characters in a cell has to do with it? And how can i fix it?
The current format I have for the first column is hh:mm:ss


Thank you!

 

[JustynaMK]

Hi,

I noticed that the formula fails to evaluate LEN(TEXT(E1,"hh:mm:ss")) part if a given cell has more than 256 characters.
Is it necessary to convert a cell to hh:mm:ss format before using the LEN function? Wouldn't simply LEN(E1) work for you?
Exemplary formula that should work for all inputs:
=IF(LEN(E1)>8,RIGHT(E1,FIND(",",E1)-1),TEXT(E1,"hh:mm:ss"))