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


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