Number converting in Excel

[pruleone]

Hi,

My excel sheet getting info from internet.
But in some cases numbers in internet is showed like this - 1.50

Excel will take it as date and if I want that number to show as percent then I will have number like 1826400,00%

To get 1,50% I need to convert 1.50 to 1,50.

Now I'm doing it with that formula: left(A1;1)+right(A1;2)/100
After that excel will show 1.50 as 1,50

My question is, is there any other way to convert 1.50 to 1,50?

I'm asking it because my formule will work untill I have one number in left and two numbers in right. If there will be two or more number at left and three or more numbers at right, then that formula doesn't work any more.

If this is the only way to get 1,50 from 1.50, then maybe someone could say how to make that formule so called pullet proof? One formula would work when my number is 1.50 or 10.4 or 9.354 and so on.

 

[SkipVought]

How about Edit > Replace?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[pruleone]

Edit and replace means that I have to replace . manualy to ,

Manualy I mean, I have to go to edit and replace and push replace all.
This solutions doesn't work for me. I need automatic converter.


But thank you your thoughts

 

[SkipVought]

Feel free to turn on your macro recorder.


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[lionelhill]

If your issue is whether the decimal separation is "." or ",", you should check the international options; in Excel2003 it's tools-options and select the international tab. I haven't looked for it in Office2007 yet. Perhaps this might help?

 

[pruleone]

that decimal separation idea doesn't work, because there are already set ","

But it seems to me also, that i need to turn on macro recorder and to somekind of macro for future.

 

[pruleone]

eheee,

there is new problem if replace "." to ","

as excel think that number with "." is date, then after changeing "." to "," excel will give out number what equals to that date.

so replacing and macro also doesn't work

 

[SkipVought]

Please post the example of the value that converts to a date: before and after.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[pruleone]

OK,

If I write 1.50, then excel will show it automaticly jaan.50
It means then 1.01.1950

If I just change format cell into number, then excel will show me 18264

If I choose replace "." to ",", when jaan.50 is showen, then excel will return me value 1,01,1950

I found that if I write number like 45.68, then excel woun't change it, because it isn't any value of date. Then will also work that idea to use change command.

So problem is with number which has date value.

 

[SkipVought]

I know that Excel does this when you enter 1-50 or 1/50, because it assumes that you are entering a date. I do not believe that Excel does this with POINTs & COMMAs, unless your cells are formatted as date before you enter these values.

Does this happen on a brand new sheet?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}