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

Ask Your Question

Replace 10,000.10 with 10.000,10

asked 2021-05-04 01:07:19 +0200

dellor gravatar image

updated 2021-05-27 15:46:20 +0200

Alex Kemp gravatar image

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 flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2021-05-04 05:40:11 +0200

edit flag offensive delete link more


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

dellor gravatar imagedellor ( 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?

Mike Kaganski gravatar imageMike Kaganski ( 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.

dellor gravatar imagedellor ( 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!)
Mike Kaganski gravatar imageMike Kaganski ( 2021-05-04 06:05:11 +0200 )edit

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

dellor gravatar imagedellor ( 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.

Mike Kaganski gravatar imageMike Kaganski ( 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.

eeigor gravatar imageeeigor ( 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)

Mike Kaganski gravatar imageMike Kaganski ( 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.

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


Mike Kaganski gravatar imageMike Kaganski ( 2021-05-04 12:28:12 +0200 )edit

answered 2021-05-04 05:42:34 +0200

eeigor gravatar image

updated 2021-05-04 13:07:16 +0200


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
edit flag offensive delete link more


Im not sure how this works.

dellor gravatar imagedellor ( 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?

eeigor gravatar imageeeigor ( 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.

dellor gravatar imagedellor ( 2021-05-05 20:43:14 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2021-05-04 01:07:19 +0200

Seen: 59 times

Last updated: May 04