I inserted numbers from a text file into column A , set the Number format for this column:

enter image description here

and try to sum the values ​​in the column with the formula:

=SUM(A1:A6)

However, the result is 0.00 . I assume that this is due to the fact that in the Number format the comma and not a period is specified as a separator for a number. How to make it possible to sum up numbers with a dot-delimiter in an excel file?

    2 answers 2

    For cases where the replacement will require a non-one-time option using the functions of the sheet.

    Before summing up, replace the delimiter:

     =СУММ(--ПОДСТАВИТЬ(0&A1:A6;".";",")) =СУММПРОИЗВ(--ПОДСТАВИТЬ(0&A1:A6;".";","))

    The first (with SUM ) is an array formula, entered using three keys - Ctrl + Shift + Enter . For the second massive input is not required.

    0 & A1: A6 - adding zero eliminates an error if the value of the cell in the specified range is empty.

    Binary negation converts a text entry to a number.

    It happens that in such values ​​a comma is used as a separator (example: 1,235.77 ). In this case, we first change the comma to empty and only after that point to a comma:

     =СУММПРОИЗВ(--ПОДСТАВИТЬ(ПОДСТАВИТЬ(0&A1:A6;",";);".";","))

      I solved this question differently: using Ctrl + H I replaced all points with commas and summed the numbers with the same formula.