Forum Discussion

JackyGrealish's avatar
JackyGrealish
Copper Contributor
Jul 13, 2022
Solved

Using IF and LEN formula for a cell

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!

excel
  • ecovonrein's avatar
    ecovonrein
    Iron Contributor
    Jul 13, 2022
    You could also try =LAMBDA(v;LET(aUnit;RIGHT(v;2);Scalar;LOOKUP(aUnit;{"kW";"MW"};{1;1000});SUBSTITUTE(v;aUnit;"")*Scalar))(G5)
  • ecovonrein's avatar
    ecovonrein
    Iron Contributor
    Jul 13, 2022

    =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.

    • JackyGrealish's avatar
      JackyGrealish
      Copper Contributor
      Jul 13, 2022
      Hi ecovonrein , I think I missed out something on what I meant in the chat above. The values taken can also be in kW. So for some instances can be for e.g. 4.7MW or can also be 880kW. But I want to create it such that any value M or k is converted to kW only. So if 4.7MW becomes 4700kW while 880kW remains as it is.

Resources