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?

 

[GraH]

Book1
	X	Y
1
2	133.81kWh	133,81
Sheet1

Cell Formulas
Range	Formula
Y2	Y2	=0+LEFT(SUBSTITUTE(X2,".",","),LEN(X2)-3)

 

[Waimea]

Hi GraH,

thank you for your reply.

Your code is sweet and I was thinking about bumping my thread with the substitute function.

I just noticed that in the last row in my data I have

"206.36Wh".

Notice the missing "k" in "Wh.

Is there a good way to make the formula aware of kWh and Wh?

 

[GraH]

one way

Book1
	X	Y
2	133.81kWh	133,81
3	206.36Wh	206,36
Sheet1

Cell Formulas
Range	Formula
Y2:Y3	Y2	=0+LEFT(SUBSTITUTE(X2,".",","),IF(ISNUMBER(FIND("k",X2)),LEN(X2)-3,LEN(X2)-2))

 

[Waimea]

Hi GraH,

thank you again your for reply!

I see what you did there with the IF formula. If I wanted to divide values without "k" with 1000.

So that 206.36Wh becomes 0,20636kWh.

Where would I add the / 1000?

 

[GraH]

would need to reverse & repeat some of the logics.

Book1
	X	Y
2	133.81kWh	133,81
3	206.36Wh	0,20636
Sheet1

Cell Formulas
Range	Formula
Y2:Y3	Y2	=IF(ISNUMBER(FIND("k",X2)),SUBSTITUTE(LEFT(X2,LEN(X2)-3),".",",")+0,SUBSTITUTE(LEFT(X2,LEN(X2)-2),".",",")/1000)

 

[Waimea]

Hi GraH,

your last piece of code is great and it does exactly what I was trying to do.

Thank you very much GraH!

 

[GraH]

Most welcome! Thanks for the feedback.
Maybe think to update your account information and add your Excel version.
Shorter solutions might be possible if you have 365 version.

 

[Waimea]

Hi GraH,

Thank you for your suggestion.

I am on Windows 10 and Excel 365.

Did you have anything shorter in mind?

 

[GraH]

I was thinking about using LET. Not shorter, but maybe more readable.

LET (
Txt , substitute(X2,".",","),
kWh , isnumber(find("k",txt)),
L, len(txt)
Nmbr, If(kWh,left(txt, L-3), left(txt,L-2))+0,
If(kwh, Nmbr, Nmbr/1000)
)