Formula returning distance based on text - search for several types of decimal separators

[chriskay]

Hi

For the attached spreadsheet example, I'm trying to make N11 return the total distance in F16-F1015. The table in C16:G1015 is based on imported reference data, and sometimes, the loaded data is being separated by both "." and "," and sometimes there are no separators.

My issue is, that the formula doesn't recognize F16 and F17, because they either don't have a decimal separator, or a comma as decimal separator:

{=IF(ISNUMBER(SEARCH(OR(".",","),$F$16:$F$1015)),(SUM(IF(ISNUMBER(FIND(OR(".",","),$F$16:$F$1015)),VALUE(LEFT($F$16:$F$1015,FIND(OR(".",","),$F$16:$F$1015)-1)),0)))/1000,(SUM(VALUE($F$16:$F$1015)/10000000)))}

In the attached example, the return value of N11 is 92.33 meters (which is incorrect), but if I change F16 to "4125.0000" and F17 to "5925.0000", it returns the correct value.

The formula is supposed to just return the SUM of the values on the left side of any eventual separators.

[XLent]

perhaps the below alternative would work for you ?

=SUMPRODUCT(LEFT(0&F16:F1015;FIND(",";SUBSTITUTE(0&F16:F1015;".";",")&",")-1)/10000000)
assumed ; delimiter per locale

[chriskay]

=SUMPRODUCT(LEFT(0&F16:F1015;FIND(",";SUBSTITUTE(0&F16:F1015;".";",")&",")-1)/10000000)

Changing the last dividing to 1000 seems to return the correct value, no matter what values I enter in Distance, so I think this is the formula I need :-)