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

#### Waimea

##### Active Member
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
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
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
Hi Peter_SSs,

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

##### MrExcel MVP, Moderator
I did however find one occurence of MWh.
What do you want to do with the number part if it is MWh?

#### Waimea

##### Active Member

Hi 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?)

#### Peter_SSs

##### MrExcel MVP, Moderator
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

Hi Peter_SSs,

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

#### Peter_SSs

##### MrExcel MVP, Moderator
You're welcome. Thanks for the confirmation.

#### Waimea

##### Active Member
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

##### MrExcel MVP, Moderator
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?

Replies
1
Views
78
Replies
7
Views
126
Replies
2
Views
79
Replies
6
Views
97
Replies
15
Views
457

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.

### Which adblocker are you using?

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

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