Replace 10,000.10 with 10.000,10

[dellor]

Imagine you are monitoring a stock price in a cell that autoupdates using the external data option, so you get 10,000.10, then after 60 seconds it updates and you get 10,020.10 or whatever.

My question is, how do I automatically get the stock price to update but switching the comma with a point and the point with a comma? because in my country we don’t use 10,020.10 but 10.020,10 but the website im using gives you 10,020.10 so I need to replace them.

I’ve tried te substitute function but it’s not working. Any helps?

[mikekaganski]

NUMBERVALUE

[dellor]

When I replace “text” with the cell (E5) I get #VALUE!

[mikekaganski]

Nice! Now it’s all clear!.. or wait! What’s in your E5? and what’s the resulting formula with the NUMBERVALUE exactly?

[dellor]

$10,000.10 is in E5 and on E6 i’ve put =NUMBERVALUE(“E5”;".";",") but gives error.

[mikekaganski]

1. It must not be "E5", but E5
2. The function does not allow the $. So you could remove the leading $ using MID(E5;2;LEN(E5)) instead of simple E5. (Note that there was no mention of the dollar sign in the original description of the task!)

[dellor]

I’ve removed the $ to test and it doesn’t work, same #VALUE! error.

[mikekaganski]

… and now your exact formula looks like … ? I am sure you didn’t follow number 1 in my comment, and didn’t replace your "E5" to E5.

[eeigor]

It works.

But LO does not understand the number format with a dot as a group separator: #.##0,00

When increasing groups, the dot is not inserted, and with this format #.###. ##0,00 extra characters appear on the left if the number is shorter.

[mikekaganski]

But LO does not understand the number format with a dot as a group separator: #.##0,00

It does. @eeigor, your problem is that you use wrong thousand separator for your locale (which is space). If you use a locale where dot is thousand separator, the format works fine; otherwise, the character that is neither decimal nor thousand separator is treated as literal text, and is used as text.

That’s the awful result of localized format codes. If we used a standard notation, where e.g. dot meant “decimal separator for your locale” and comma “thousand separator for your locale”, then for ru-RU, #,##0.00 would give 1 234,45; for en-US, it would be 1,234.45, and for de-DE, 1,234.45. But we have what we have: different codes for each locale, a zoo expecting actual separator characters: # ##0,00 for ru-RU, #,##0.00 for en-US, #.##0,00 for de-DE.

[eeigor]

Then converting the text to a number is enough. The rest is done by the format in its locale.

[mikekaganski]

Exactly.⁠⁠

[dellor]

Can someone post a working .ods converting 10,000.10 to 10.000,10?

[mikekaganski]

1000010to1000010.ods

[eeigor]

…

Sub Point2Comma()
            	Dim	oSelection As Object  'com.sun.star.uno.XInterface
            	Dim oReplace As Object  'com.sun.star.util.XReplaceDescriptor
            	Dim nCount As Long
        
        	oSelection = ThisComponent.CurrentSelection
        REM	Xray oSelection
        	If Not (oSelection.supportsService("com.sun.star.sheet.SheetCellRange") _
        	 Or oSelection.supportsService("com.sun.star.sheet.SheetCellRanges")) Then
        		MsgBox "Range is not selected. Multiple selection allowed." _
        		 , MB_ICONEXCLAMATION, "Selection Error"
        		Exit Sub
        	End If
        
        	oReplace = ThisComponent.CurrentController.ActiveSheet.createReplaceDescriptor()
        	With oReplace  ': .SearchRegularExpression = False: .SearchWords = False
        		.SearchString = "."  '.setSearchString(".")
        		.ReplaceString = ","  '.setReplaceString(",")
        	End With
        
        	' Set decimal number or currency format with group separator (#,##0.00).
        	oSelection.NumberFormat = com.sun.star.i18n.NumberFormatIndex.NUMBER_1000DEC2 
        	nCount = oSelection.replaceAll(oReplace)
        	MsgBox "Number of replacements: " & nCount, MB_ICONINFORMATION, "Done"
        End Sub

[dellor]

Im not sure how this works.

[eeigor]

You must use macros. But I didn’t take into account the leading $ sign and the group separator.

Easy to fix… But a little later.

However, if dynamic updating is required, it is better to use the NUMBERVALUE formula, it is good if the converted data lies in a single column and the number of rows in the range does not change. How is the data located?

[dellor]

I have found another site that gives you the stock prices in the format I want by default. However just find any website that gives you a xx,xxx.xx format for a price and put it on a cell then try to convert it to xx.xxx,xx

I couldn’t so gave up and found another site.