Operate with number in excel cell, which has number and text

Question

I am having a trouble with code below.

Firstly, I am trying to replace symbol "," with "." because I want to have "," everywhere instead "." but it does not affect the change in excel cells.

Second, I am trying to convert grams to kilograms in excel column. Empty space is cell with no information. Look at dots too. They have to get up commas.

Current column:
140 g
2.68 kg
3,52 kg
5.7 kg
12,45 kg
360 g
350 g
129 g
75 g
0,095 kg
98,6 g


33 g
395 g
470 g
557 g
1.416 kg
2,021 kg

====================

Excepted result:
0,14 kg
2,68 kg
3,52 kg
5,7 kg
12,45 kg
0,36 kg
0,35 kg
0,129 kg
0,075 kg
0,095 kg

0,033 kg
0,395 kg
0,47 kg
0,557 kg
1,416 kg
2,021 kg
0,0986 kg

====================

Last working update at 10.04.2022 (For better solutions, write comment)

import openpyxl


def converter(excel_path):
    inv_file = openpyxl.load_workbook(excel_path)
    product_list = inv_file.active

    for row in range(2, product_list.max_row + 1):
        weights = product_list.cell(row, 18).value  # column number

        if weights is None:
            print("Empty cell")
        else:
            for value in weights.split():
                if value == "g":
                    try:
                        new_value = str(weights[:-1])  # I am removing "g" at end, to calculate without string
                        conversion = float(new_value) / 1000
                        new_weight = (str(conversion) + " kg")
                        weights = new_weight.replace(".", ",")
                        product_list.cell(row, 18).value = weights
                    except:
                        print("Invalid value")
                else:
                    weights = weights.replace(".", ",")
                    product_list.cell(row, 18).value = weights

            print(weights)

    inv_file.save("converted_table_export.xlsx")


if __name__ == '__main__':
    converter("convert_table.xlsx")

Answer 1

I removed the Excel code so I can help with the conversion. I just made a list of the three examples you provided. Review my code and update your code accordingly. UPDATE: I corrected one of the lines below as discussed in the comments below.

weights = ["140 g", "2,68 kg",""]

for weight in weights:

    if weight == "":
        print("Weight: empty")
    else:
        # replace a , in cell with .
        weight = weight.replace(",", ".")

        text_separator = weight.split()

        # [0] is for getting number
        # [1] is for "g" or "kg"
        if text_separator[1] == "g":
            new_value = (str(float(text_separator[0]) / 1000)+" kg").replace(".",",")
        else:
            new_value = weight
        print("Weight: {}  New Weight: {}".format(weight,new_value))

Output:

Weight: 140 g  New Weight: 0,14 kg
Weight: 2,68 kg  New Weight: 2,68 kg
Weight: empty