Replace [SOLVED]

[madocar]

Hi everyone,

I need to change number from 1.9 to 1,9. When I have the value 1.9 excel takes it as the date 1.9.2013 because I have a Slovak version.

I tried to simply replace "." to "," but when I did it I got the value 41518.

I want to change a lot of these wrong values all at once.. could you help me please?

 

[Hui]

Madocar


You could try using Custom Number Formats

Select the cells

Ctrl 1

Number,

Custom

enter 0,0 or #,# into the Custom Number format dialog

 

[madocar]

Doesn't work.

It changes the value to 41518

File on link


http://www45.zippyshare.com/v/9468715/file.html

 

[gauravkrgautam]

Hi Madocar & Hui Sir,


sorry for interepting in between


please follow the link

http://rapidshare.com/files/2530159875/replace.xlsx

if it works!!!

 

[Faseeh]

Hi All,


Try custom formatting dates as

d.m

only.


Regards,

 

[madocar]

Thank you guys so much.

I thought it would not be possible.

But the formula text(B4;"m.d") changed my mind

It was kind of complicated but I modified the formula a bit and now it works very well

 

[bobhc]

@madocar


Just as a matter of interest what part of =TEXT(B3,"D.M") is complicated !!??

 

[madocar]

@bobhc


My excel file was complicated not that formula.

Need to be write more understandable especially for u

 

[gauravkrgautam]

Hi Madocar,


Did you got answer you are looking for.

 

[bobhc]

@madocar


I read it that you found the formula complicated because you said you had to modified the formula, the only thing I could think you could "modify" in such a small formula would be the cell reference, I can assure you do not need to "write more understandable" just for me alone.


@gauravkrgautam

I take it that madocar has used you solution which is clean and to the point, even without modifying it

 

[madocar]

@gauravkrgautam


Yes! I did.

Once again thank you