Using IF and LEN formula for a cell

Occasional Contributor


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?


7 Replies

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.

You could also try =LAMBDA(v;LET(aUnit;RIGHT(v;2);Scalar;LOOKUP(aUnit;{"kW";"MW"};{1;1000});SUBSTITUTE(v;aUnit;"")*Scalar))(G5)
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.
Yes? My suggestions do that. Unless you actually want the letter "kW" in the result, in which case you still need to append them. But the answers are in kW.
best response confirmed by JackyGrealish (Occasional Contributor)


As variant

=LEFT( A1, LEN(A1) - 2)*IF(RIGHT(A1,2) = "MW", 1000, 1)
Thanks for this!

@JackyGrealish , you are welcome