 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!

7 Replies

# Re: Using IF and LEN formula for a cell

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

# Re: Using IF and LEN formula for a cell

You could also try =LAMBDA(v;LET(aUnit;RIGHT(v;2);Scalar;LOOKUP(aUnit;{"kW";"MW"};{1;1000});SUBSTITUTE(v;aUnit;"")*Scalar))(G5)

# Re: Using IF and LEN formula for a cell

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.

# Re: Using IF and LEN formula for a cell

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)
Solution

# Re: Using IF and LEN formula for a cell

As variant

``=LEFT( A1, LEN(A1) - 2)*IF(RIGHT(A1,2) = "MW", 1000, 1)``

Thanks for this!

# Re: Using IF and LEN formula for a cell

@JackyGrealish , you are welcome