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

Waimea

Active Member
Joined
Jun 30, 2018
Messages
447
Office Version
  1. 365
Platform
  1. Windows
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?
 
Last edited:

Waimea

Active Member
Joined
Jun 30, 2018
Messages
447
Office Version
  1. 365
Platform
  1. Windows
Hi GraH,

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

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,689
Office Version
  1. 365
Platform
  1. Windows
Did you have anything shorter in mind?
If the number part always has 2 decimal places like your samples, try

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

Edit:
.. or 3 characters shorter. :)
Excel Formula:
=SUBSTITUTE(LEFT(X2;FIND(".";X2)+2);".";",")/1000^(RIGHT(X2;3)<>"kWh")
 
Last edited:

Waimea

Active Member
Joined
Jun 30, 2018
Messages
447
Office Version
  1. 365
Platform
  1. Windows
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?
 

Waimea

Active Member
Joined
Jun 30, 2018
Messages
447
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,689
Office Version
  1. 365
Platform
  1. Windows
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 🤞 )
Excel Formula:
=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

Active Member
Joined
Jun 30, 2018
Messages
447
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi Peter_SSs,

thank you for your reply.

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

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,689
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the confirmation. :)
 

Waimea

Active Member
Joined
Jun 30, 2018
Messages
447
Office Version
  1. 365
Platform
  1. Windows
Hi again,

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



5.71kWh766.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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,689
Office Version
  1. 365
Platform
  1. Windows
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?
 

Forum statistics

Threads
1,147,453
Messages
5,741,214
Members
423,649
Latest member
steel1968

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top