We will be migrating from Ask to Discourse on the first week of August, read the details here

# Replace 10,000.10 with 10.000,10

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?

edit retag close merge delete

Sort by » oldest newest most voted
more

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

( 2021-05-04 05:48:56 +0200 )edit

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

( 2021-05-04 05:50:27 +0200 )edit

$10,000.10 is in E5 and on E6 i've put =NUMBERVALUE("E5";".";",") but gives error. ( 2021-05-04 05:55:31 +0200 )edit 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!) ( 2021-05-04 06:05:11 +0200 )edit I've removed the$ to test and it doesn't work, same #VALUE! error.

( 2021-05-04 06:29:36 +0200 )edit

... 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.

( 2021-05-04 06:31:21 +0200 )edit

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.

( 2021-05-04 12:07:13 +0200 )edit

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 ...(more)

( 2021-05-04 12:16:00 +0200 )edit

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

( 2021-05-04 12:24:00 +0200 )edit

Exactly.⁠⁠

( 2021-05-04 12:28:12 +0200 )edit

..

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

more

Im not sure how this works.

( 2021-05-04 05:52:14 +0200 )edit

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?

( 2021-05-04 06:56:40 +0200 )edit

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.

( 2021-05-05 20:43:14 +0200 )edit