Jul 12 2022 06:40 PM
Hi,
I work at a energy company where I need to paste value of system sizes, for eg. 8.73MW . I used formula:
=LEFT(G5,LEN(G5)-2) to remove the 'MW'. And used IF to see if value is below 30, multiply the value by 1000 to basically change MW to kW. IF(G7<30,G7*1000,G7). Dont mind about the cell numbers as I randomly took from my excel sheet. Is there a way where I can combine both IF and LEN formula such that when I paste 8.73MW, it automatically removes MW and multiply it by 1000?
Thanks!
Jul 12 2022 07:06 PM - edited Jul 12 2022 07:25 PM
=SUBSTITUTE(G5;"MW";"000")*1
Mind, that only works if people don't use space. I might observe that it is poor style to put the unit measure into the same field as the quantity. Better split across two cells.
Jul 12 2022 07:25 PM
Jul 12 2022 10:57 PM
Jul 13 2022 06:47 AM
Jul 13 2022 06:59 AM
SolutionJul 13 2022 06:59 AM
SolutionAs variant
=LEFT( A1, LEN(A1) - 2)*IF(RIGHT(A1,2) = "MW", 1000, 1)