Replace the [,] into [.]

[sachar]

Dear All,

With reference to the attachment some value highlighted with the yellow colour of the column ”J” . I would like to replace the coma [,] into dot [.] so, get the figure in hundred like others.

Please let me If, there is any formula?

 

[Chihiro]

You can do paste special operation...

Put 1000 in L1 (or other out of way cell) and copy.

CTRL + Left click to select 3 cells (J43, 49 & 52).

Right click and "Paste Special". Paste = Value, Operation = Divide.

Hit Ok.

Delete L1.

 

[sachar]

Dear All,

Received with thanks, I have to do one by one with this system. Can be possible by one sort?

 

[bobhc]

What's wrong with a simple find and replace?

 

[Chihiro]

@sachar
Hmm? Not sure what you mean.

If you mean select range... I'd sort by value in J (largest to smallest) and select range to paste. But since you have blank rows mixed in your range. I'd not recommend it.

Another method is to use formula in Column K (starting at K12)....
=IF(J12>=1000,J12/1000,J12)

@bobhc
His thread title is misleading. It's cell formatting with "," as thousand separator. Not actual "," as text. So Find & Replace won't work here.
Edit: Unless replacing "000" with blank. But I suspect value less than 1000 will not always be 0 in actual data.

 

[sachar]

Dear Chihiro,

Thanking you, You made so simple.

 

[bobhc]

@Chihiro, my thanks must admit did not fully open the thread.

But if the OP copy's the character and pastes it in the find and replace it will work, find and replace works on Alpha and Numeric.

 

[AVK]

>Select column "J"
>Go to "Format"
>Select "Format cells"
>Select "Number"
>Uncheck "use 1000 separator (,)
>click "ok"