unable to replace , with .

[villival]

Hi All,

i am having huge list of numbers which need to be replace , with .

find below my requirement when trying +H am getting error please help me to resolve this

51,200
10,240
40,960
needs replaced

51.200
10.240
40.960

 

[Ramesh]

Try this approach:

Copy those numbers into a note pad and perform your find replace operation..

Then copy the data back to the excel worksheet.

 

[villival]

Ramesh

i tried this but its working some data is missing

when i copy the data to notepad 51.200 this will become 51.2

 

[Ramesh]

Yeah true..

after copy/pasting the data from notepad onto Excel ..increase the decimal values for the numbers to 3

 

[villival]

51,200 51.2
10,240 10.24
40,960 40.96
1,02,400 1.02.400
1,02,400 1.02.400
1,02,400 1.02.400
1,02,400 1.02.400
1,02,400 1.02.400
1,02,400 1.02.400
cant do it i tried the numbers are different

 

[bobhc]

If you had taken just a moment or two to help yourself instead of waiting for members to do it all for you, you would have seen this post just a few places below yours


http://forum.chandoo.org/threads/replace-the-into.32582/
.

 

[Mark de Nooijer]

a comma actually implies a number that isn't a integer.
Just multiply the column with 1000 and cell format the output to display a . as a Thousand separation mark.

 

[bosco_yip]

Hi,

List of numbers
51,200
10,240
40,960
1,02,400

If list of numbers are text value >>
Using "Find and Replace" to replace "," with " " (blank)
--> convert to numeric values

Become >>

51200
10240
40960
102400

Select list of numeric values>> Custom cells format, in the type box enter :

[>=10000000]##\.##\.##\.##0;[>=100000] ##\.##\.##0;##\.##0

Become >>

51.200
10.240
40.960
1.02.400

Regards

 

[Khalid NGO]

Hi @villival

If these are real numbers (not text values) and the issue pertains to all workbooks then go to Excel Option, check Decimal and Thousands separator symbols:



Regards,

 

[Gary's Student]

Select the cells you wish to process and run this short macro:

Sub xlate()
    Dim r As Range
    For Each r In Selection
        r.Value = "'" & Replace(r.Text, ",", ".")
    Next r
End Sub