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.

 

[GerrardSVK]

OK I ran the code on column D (D9:D136).
Before:

2.kus_kavita3.xlsx
	D
9	73.300°
10	5.5
11	51.6
12	48
13	38.3
14	36.95
15	0
16	30
17	0
18	29
19	20.8
20	16.3
21	10.4
22	6
23	6
24	10
25	2
26	16
27	22.6
28	1.9
29	16.4
30	10.1
31	0
32	6
33	6
34	10.4
35	16.2
36	10
37	16
38	22.6
39	1.9
40	30
41	9
42	2
43	0
44	0
45	0
46	0
47	0
48	16.3
49	10.4
50	6
51	6
52	10
53	2
54	16
55	22.6
56	1.9
57	16.4
58	10.1
59	0
60	6
61	6
62	10.4
63	16.2
64	10
65	16
66	22.6
67	1.9
68	30
69	9
70	2
71	0
72	0
73	0
74	0
75	0
76	16.15
77	21.4
78	12
79	11.5
80	25
81	17
82	1.8
83	21.5
84	15.9
85	0
86	2.3
87	12
88	12
89	16.3
90	21.3
91	11.5
92	17
93	25
94	1.8
95	30
96	3.7
97	12.7
98	12
99	2.7
100	0
101	0
102	0
103	0
104	0
105	13.9
106	11.9
107	11
108	11.7
109	0.8
110	0.8
111	30.000°
112	3
113	1.2
114	90.000°
115	2.8
116	2.8
117	24.9
118	25.8
119	27.8
120	21
121	11.2
122	13.1
123	13.9
124	14
125	6
126	12
127	4
128	3.25
129	250
130	116.3
131	58.6
132	252.7
133	62.3
134	142.5
135	2.9
136	2.9
2.kus_kavita3

After:

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

WTF It didnt work for me like this :D if i have number for example 73.541° result is 73 541

 

[kevin9999]

WTF It didnt work for me like this :D if i have number for example 73.541° result is 73 541

It might have something to do with regional/local settings. In any event, I think I've reached the limit of what help I can render. Hopefully someone else will have an answer for you. Good luck, and best wishes.

 

[GerrardSVK]

It might have something to do with regional/local settings. In any event, I think I've reached the limit of what help I can render. Hopefully someone else will have an answer for you. Good luck, and best wishes.

Thank you anyway you help maybe I will find it on my own.

 

[kevin9999]

Thank you anyway you help maybe I will find it on my own.

The result you got seems to suggest that the period . was replaced with a space " " instead of a comma ,

 

[GerrardSVK]

The result you got seems to suggest that the period . was replaced with a space " " instead of a comma ,

Sub test()
  Call ReplaceDecimal(Workbooks(3).ActiveSheet.Range("G9:G136"))
  Call ReplaceDegree(Workbooks(3).ActiveSheet.Range("G9:G136"))
End Sub
Sub ReplaceDecimal(rng As Object)
  Dim r As Range
  For Each r In rng
    r.Value = "'" & Replace(r.Text, ".", ",")
  Next
End Sub
Sub ReplaceDegree(rng As Range)
  Dim r As Range
  For Each r In rng
    r.Value = "'" & Replace(r.Text, "°", "")
  Next
End Sub

Hi I made this work for me but it make not properlz form of data I need this work that it converts number from for example 75.258° into 75,258 but there is something like change mark in cell.

see image. How do I get rid of it? Dont you know?

 

[MARK858]

What does the formula

=UNICODE(RIGHT(F9,1))

return (assuming the cell the arrow is pointing at is F9)?

 

[JulieBunavicz]

Try something like this

=REPLACE(A2,10,1,"")

Description of formula:
A2 = Cell
Number of spaces to where you want new char
# of new char
Char ","

Kind regards,

Julie Bunavicz

 

[Scott Huish]

If you are asking quite literally how to get rid of the "change mark", I'm assuming you mean the green arrow, you need to turn off background error checking.

 

[Legacy 143009]

If you are asking quite literally how to get rid of the "change mark", I'm assuming you mean the green arrow, you need to turn off background error checking.

I am afraid that he is not asking about the green arrow. He asking about "'".

@GerrardSVK
This is the mark that makes your value become a text and make the necessary changes. Excel is warning you because it is not a value anymore.
If you want to switch from "." to "," and still keep it a decimal number, there are only 3 legit ways:
1. Go to Windows Control Panel and change the delimiter preference from your regional settings.
2. Go to Excel settings. Click Advanced. Under Editing options, clear the Use system separators check box. Change the default Decimal separator.
3. Convert your data into a table. Select the table and Go to Data tab then click Import Data from Table or Range. In the query window Right Click to column. Select Replace and replace the degree sign. Then change the column type using Locale Settings and select a country like Turkey where the decimal seperator is ",". Save and Close the query.

These are the best options I can think of.

 

[Scott Huish]

The apostrophe to make it text? Copy a blank cell. Select the range with the numbers preceded with the quote mark. Right click and click on Paste Special, then select Add.