Can't convert . into , using find and replace?

[Waimea]

Hi,

I am trying to convert "text"?? into number and I am trying to use find and replace but I get no results.

In cell X2 I have the string "133.81kWh".

In cell Y2 I have the following formula "=TRIM(LEFT(X2;LEN(X2)-3))"

I want to convert 133.81kWh to 133,81.

I have tried changing the format from text to number, I have tried to find and replace . with ,. I have also tried paste as special and multiplying the string with 1.0.

I am looking at a cell with the formula =ISNUMBER(Y2) which gives me FALSE.

How should I do to convert the string 133.81kWh to 133,81?

 

[Waimea]

Hi GraH,

I haven't seen LET before but it seems powerful!

 

[Peter_SSs]

Did you have anything shorter in mind?

If the number part always has 2 decimal places like your samples, try

=SUBSTITUTE(LEFT(X2;FIND(".";X2)+2);".";",")/(1000^ISERROR(FIND("k";X2)))

Edit:
.. or 3 characters shorter.

=SUBSTITUTE(LEFT(X2;FIND(".";X2)+2);".";",")/1000^(RIGHT(X2;3)<>"kWh")

 

[Waimea]

Hi Peter_SSs,

thank you for your reply!

I am not sure if the number part always has 2 decimal places.

I did however find one occurence of MWh.

So I have most values (99%) are kWh, a couple of values are Wh. And 1 value is Mwh.

I guess I would need another IF statement to account for this?

 

[Peter_SSs]

I did however find one occurence of MWh.

What do you want to do with the number part if it is MWh?

 

[Waimea]

Hi Peter_SSs,

thank you for your fast reply!

If the value is MWh I would like to divide it by 1000 and convert the number to kWh (is my math right?)

 

[Peter_SSs]

If the value is MWh I would like to divide it by 1000 and convert the number to kWh (is my math right?)

I think that you might need to multiply by 1000 for that?

Try this (I'm struggling with the formula conversion because my system uses "." as the decimal separator and "," as the formula argument delimiter so ? )

=LET(R_3;MID(X2;LEN(X2)-2;1);SUBSTITUTE(LEFT(X2;LEN(X2)-3+ISNUMBER(R_3+0));".";",")*IF(R_3="k";1;IF(R_3="M";1000;1/1000)))

 

[Waimea]

Hi Peter_SSs,

thank you for your reply.

Your code works flawlessly and your math is correct, I want to multiply!

 

[Peter_SSs]

You're welcome. Thanks for the confirmation.

 

[Waimea]

Hi again,

I am looking at this formula again and I am not sure what it gives me?

5.71kWh

766.87Wh

4,94313​

It seems that I am getting wrong values, I subtract 7.668Wh from 5.71kWh and I get 4.94313.

Am I doing something wrong?

 

[Peter_SSs]

I am looking at this formula again

Which formula - the one from post #2 that you have marked as the solution, or the one from post #16 or something else?

I subtract 7.668Wh from 5.71kWh

How exactly have you done that? What about a few samples and expected results with XL2BB so the we can copy to test with?