Replace function for partial characters

[GerrardSVK]

Hi everyone could someone help me I just need to write simple macro that will change number from this format: 1.234 into format using comma instead of dot : 1,234
Also I need to change sign of degrees into nothing. So if there will be some number like this 1.234° final result will be 1,234.

I tried this but dont work:
Sub Replace()

Workbooks(2).ActiveSheet.Range("G9:G136").Replace What:=".", Replacement:=","
Workbooks(2).ActiveSheet.Range("G9:G136").Replace What:="°", Replacement:=""

End Sub

I think there have to be additional xlPart defined or something.

 

[Peter_SSs]

@GerrardSVK
When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags in post #10 for you this time.

 

[kevin9999]

Your method of referring to your sheet with the values you need to change is messy. You should always strive to be explicit with your object references. Please try the following - just change the workbook/worksheet names to suit.

Sub Fix_Format()
    Dim wb As Workbook, ws As Worksheet
    Set wb = Workbooks("Workbook_Name")         '<-- *** Change to actual workbook name ***
    Set ws = wb.Worksheets("Sheet1")            '<-- *** Change to actual worksheet name ***
        With ws.Range("G9:G136")
            .Value2 = Evaluate("Substitute(Substitute(" & .Address(, , , 1) & ",""°"",""""),""."","","")")
        End With
End Sub

 

[GerrardSVK]

Your method of referring to your sheet with the values you need to change is messy. You should always strive to be explicit with your object references. Please try the following - just change the workbook/worksheet names to suit.

Sub Fix_Format()
    Dim wb As Workbook, ws As Worksheet
    Set wb = Workbooks("Workbook_Name")         '<-- *** Change to actual workbook name ***
    Set ws = wb.Worksheets("Sheet1")            '<-- *** Change to actual worksheet name ***
        With ws.Range("G9:G136")
            .Value2 = Evaluate("Substitute(Substitute(" & .Address(, , , 1) & ",""°"",""""),""."","","")")
        End With
End Sub

Hi but I cant use exact name of workbook because it will change with using this macro so this is why I use wrokbook marking by number. My point is you open exel file named A take numbers in format using dot instead of commas convert them to numbers using comma and erease degree mark then I need to copy that data to another workbook then I will close exel file A and then open exel file B(this file has same content like A but different values) and I need to use same macro so It has to by name free makro.

 

[kevin9999]

I think I understand you. OK, try this:

Sub Fix_Format()
    Dim wb As Workbook, ws As Worksheet
    Set wb = Workbooks(2)
    Set ws = wb.ActiveSheet
        With ws.Range("G9:G136")
            .Value2 = Evaluate("Substitute(Substitute(" & .Address(, , , 1) & ",""°"",""""),""."","","")")
        End With
End Sub

 

[GerrardSVK]

I think I understand you. OK, try this:

Sub Fix_Format()
    Dim wb As Workbook, ws As Worksheet
    Set wb = Workbooks(2)
    Set ws = wb.ActiveSheet
        With ws.Range("G9:G136")
            .Value2 = Evaluate("Substitute(Substitute(" & .Address(, , , 1) & ",""°"",""""),""."","","")")
        End With
End Sub

Hi it works but not corectly It changes not decimal but thousands for me situation is following. I have decimal number 1.234° and the reasult have to be 1,234 from you code it changes to tousands 1 234.

 

[kevin9999]

Can you share your file via Google Drive, Dropbox or similar file sharing platform? The format of your values is obviously different from what I've been testing with. When I run the code, it changes from this:

Book1
	G
9	1.245°
10	1.245°
11	1.245°
12	1.245°
13	1.245°
14	1.245°
15	1.245°
16	1.245°
17	1.245°
18	1.245°
19	1.245°
20	1.245°
21	1.245°
22	1.245°
23	1.245°
24	1.245°
25	1.245°
26	1.245°
27	1.245°
28	1.245°
29	1.245°
30	1.245°
31	1.245°
32	1.245°
33	1.245°
34	1.245°
35	1.245°
36	1.245°
37	1.245°
38	1.245°
39	1.245°
40	1.245°
41	1.245°
42	1.245°
43	1.245°
44	1.245°
45	1.245°
46	1.245°
47	1.245°
48	1.245°
49	1.245°
50	1.245°
51	1.245°
52	1.245°
53	1.245°
54	1.245°
55	1.245°
56	1.245°
57	1.245°
58	1.245°
59	1.245°
60	1.245°
61	1.245°
62	1.245°
63	1.245°
64	1.245°
65	1.245°
66	1.245°
67	1.245°
68	1.245°
69	1.245°
70	1.245°
71	1.245°
72	1.245°
73	1.245°
74	1.245°
75	1.245°
76	1.245°
77	1.245°
78	1.245°
79	1.245°
80	1.245°
81	1.245°
82	1.245°
83	1.245°
84	1.245°
85	1.245°
86	1.245°
87	1.245°
88	1.245°
89	1.245°
90	1.245°
91	1.245°
92	1.245°
93	1.245°
94	1.245°
95	1.245°
96	1.245°
97	1.245°
98	1.245°
99	1.245°
100	1.245°
101	1.245°
102	1.245°
103	1.245°
104	1.245°
105	1.245°
106	1.245°
107	1.245°
108	1.245°
109	1.245°
110	1.245°
111	1.245°
112	1.245°
113	1.245°
114	1.245°
115	1.245°
116	1.245°
117	1.245°
118	1.245°
119	1.245°
120	1.245°
121	1.245°
122	1.245°
123	1.245°
124	1.245°
125	1.245°
126	1.245°
127	1.245°
128	1.245°
129	1.245°
130	1.245°
131	1.245°
132	1.245°
133	1.245°
134	1.245°
135	1.245°
136	1.245°
Sheet1

To this:

Book1
	G
9	1,245
10	1,245
11	1,245
12	1,245
13	1,245
14	1,245
15	1,245
16	1,245
17	1,245
18	1,245
19	1,245
20	1,245
21	1,245
22	1,245
23	1,245
24	1,245
25	1,245
26	1,245
27	1,245
28	1,245
29	1,245
30	1,245
31	1,245
32	1,245
33	1,245
34	1,245
35	1,245
36	1,245
37	1,245
38	1,245
39	1,245
40	1,245
41	1,245
42	1,245
43	1,245
44	1,245
45	1,245
46	1,245
47	1,245
48	1,245
49	1,245
50	1,245
51	1,245
52	1,245
53	1,245
54	1,245
55	1,245
56	1,245
57	1,245
58	1,245
59	1,245
60	1,245
61	1,245
62	1,245
63	1,245
64	1,245
65	1,245
66	1,245
67	1,245
68	1,245
69	1,245
70	1,245
71	1,245
72	1,245
73	1,245
74	1,245
75	1,245
76	1,245
77	1,245
78	1,245
79	1,245
80	1,245
81	1,245
82	1,245
83	1,245
84	1,245
85	1,245
86	1,245
87	1,245
88	1,245
89	1,245
90	1,245
91	1,245
92	1,245
93	1,245
94	1,245
95	1,245
96	1,245
97	1,245
98	1,245
99	1,245
100	1,245
101	1,245
102	1,245
103	1,245
104	1,245
105	1,245
106	1,245
107	1,245
108	1,245
109	1,245
110	1,245
111	1,245
112	1,245
113	1,245
114	1,245
115	1,245
116	1,245
117	1,245
118	1,245
119	1,245
120	1,245
121	1,245
122	1,245
123	1,245
124	1,245
125	1,245
126	1,245
127	1,245
128	1,245
129	1,245
130	1,245
131	1,245
132	1,245
133	1,245
134	1,245
135	1,245
136	1,245
Sheet1

 

[GerrardSVK]

Can you share your file via Google Drive, Dropbox or similar file sharing platform? The format of your values is obviously different from what I've been testing with. When I run the code, it changes from this:

Book1
	G
9	1.245°
10	1.245°
11	1.245°
12	1.245°
13	1.245°
14	1.245°
15	1.245°
16	1.245°
17	1.245°
18	1.245°
19	1.245°
20	1.245°
21	1.245°
22	1.245°
23	1.245°
24	1.245°
25	1.245°
26	1.245°
27	1.245°
28	1.245°
29	1.245°
30	1.245°
31	1.245°
32	1.245°
33	1.245°
34	1.245°
35	1.245°
36	1.245°
37	1.245°
38	1.245°
39	1.245°
40	1.245°
41	1.245°
42	1.245°
43	1.245°
44	1.245°
45	1.245°
46	1.245°
47	1.245°
48	1.245°
49	1.245°
50	1.245°
51	1.245°
52	1.245°
53	1.245°
54	1.245°
55	1.245°
56	1.245°
57	1.245°
58	1.245°
59	1.245°
60	1.245°
61	1.245°
62	1.245°
63	1.245°
64	1.245°
65	1.245°
66	1.245°
67	1.245°
68	1.245°
69	1.245°
70	1.245°
71	1.245°
72	1.245°
73	1.245°
74	1.245°
75	1.245°
76	1.245°
77	1.245°
78	1.245°
79	1.245°
80	1.245°
81	1.245°
82	1.245°
83	1.245°
84	1.245°
85	1.245°
86	1.245°
87	1.245°
88	1.245°
89	1.245°
90	1.245°
91	1.245°
92	1.245°
93	1.245°
94	1.245°
95	1.245°
96	1.245°
97	1.245°
98	1.245°
99	1.245°
100	1.245°
101	1.245°
102	1.245°
103	1.245°
104	1.245°
105	1.245°
106	1.245°
107	1.245°
108	1.245°
109	1.245°
110	1.245°
111	1.245°
112	1.245°
113	1.245°
114	1.245°
115	1.245°
116	1.245°
117	1.245°
118	1.245°
119	1.245°
120	1.245°
121	1.245°
122	1.245°
123	1.245°
124	1.245°
125	1.245°
126	1.245°
127	1.245°
128	1.245°
129	1.245°
130	1.245°
131	1.245°
132	1.245°
133	1.245°
134	1.245°
135	1.245°
136	1.245°
Sheet1

To this:

Book1
	G
9	1,245
10	1,245
11	1,245
12	1,245
13	1,245
14	1,245
15	1,245
16	1,245
17	1,245
18	1,245
19	1,245
20	1,245
21	1,245
22	1,245
23	1,245
24	1,245
25	1,245
26	1,245
27	1,245
28	1,245
29	1,245
30	1,245
31	1,245
32	1,245
33	1,245
34	1,245
35	1,245
36	1,245
37	1,245
38	1,245
39	1,245
40	1,245
41	1,245
42	1,245
43	1,245
44	1,245
45	1,245
46	1,245
47	1,245
48	1,245
49	1,245
50	1,245
51	1,245
52	1,245
53	1,245
54	1,245
55	1,245
56	1,245
57	1,245
58	1,245
59	1,245
60	1,245
61	1,245
62	1,245
63	1,245
64	1,245
65	1,245
66	1,245
67	1,245
68	1,245
69	1,245
70	1,245
71	1,245
72	1,245
73	1,245
74	1,245
75	1,245
76	1,245
77	1,245
78	1,245
79	1,245
80	1,245
81	1,245
82	1,245
83	1,245
84	1,245
85	1,245
86	1,245
87	1,245
88	1,245
89	1,245
90	1,245
91	1,245
92	1,245
93	1,245
94	1,245
95	1,245
96	1,245
97	1,245
98	1,245
99	1,245
100	1,245
101	1,245
102	1,245
103	1,245
104	1,245
105	1,245
106	1,245
107	1,245
108	1,245
109	1,245
110	1,245
111	1,245
112	1,245
113	1,245
114	1,245
115	1,245
116	1,245
117	1,245
118	1,245
119	1,245
120	1,245
121	1,245
122	1,245
123	1,245
124	1,245
125	1,245
126	1,245
127	1,245
128	1,245
129	1,245
130	1,245
131	1,245
132	1,245
133	1,245
134	1,245
135	1,245
136	1,245
Sheet1

Try this link there are 2 files Konverter is that where VBA code is writen and 1.kus cavita3 is that I will change format in it.

Je to tam. Úspěšně uloženo

Ulož.to Disk je československou jedničkou pro svobodné sdílení souborů. Nahrávejte, sdílejte a stahujte zdarma.

uloz.to

 

[kevin9999]

Deleted - got the file now.

 

[GerrardSVK]

Deleted - got the file now.

Do you have it?

If not try this.

Ulož.to Disk

Ulož.to Disk je československou jedničkou pro svobodné sdílení souborů. Nahrávejte, sdílejte a stahujte zdarma.

uloz.to

 

[kevin9999]

OK. I'm not sure that I can be of any further assistance. The file you shared is a comma delimited csv with all the values in column A - I'm not sure what process you undertake to get those values into column G so as to match your original post requirements. In any event, I ran the code on column A to see whether it would remove the degree symbol & convert the periods to commas - and it seemed to. Therefore, I cannot duplicate the issue you're having with the code. Here's a link to the file you shared after I ran the code on column A.
1.kus_kavita.csv