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, multipl...
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Jul 13, 2022

    JackyGrealish 

    As variant

    =LEFT( A1, LEN(A1) - 2)*IF(RIGHT(A1,2) = "MW", 1000, 1)
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)

Resources