How can convert text "(12345678) " to number in the format of "-12,345,678.00"?

luofeiyu1 · September 2, 2025, 3:19pm

Convert a text string “12345678” to nmuber in the format of “12,345,678.00”:
All important part keeped.

    Dim Locale as New com.sun.star.lang.Locale
    Locale.Country = "US"
    Locale.Language = "en"
    oCell.string = "12345678"
    DataVal = CDbl(oCell.string)
    FormatNumber = ThisComponent.NumberFormats.queryKey("#,##0.00", Locale, True)
    oCell.value = DataVal
    oCell.NumberFormat = FormatNumber
    msgbox oCell.string

How can convert text "(12345678) " to number in the format of “-12,345,678.00”?

The below code can’t work.

    Dim Locale as New com.sun.star.lang.Locale
    Locale.Country = "US"
    Locale.Language = "en"
    oCell.string = "(12345678)"
    DataVal = CDbl(oCell.string)
    FormatNumber = ThisComponent.NumberFormats.queryKey("#,##0.00", Locale, True)
    oCell.value = DataVal
    oCell.NumberFormat = FormatNumber

How to fix it?

sokol92 · September 2, 2025, 3:47pm

Your question mixes up the concepts of cell value and cell format.
The value of a non-empty cell (not a formula) is a number or text (there are also error values).
Formatting a cell cannot change its value.
If you want to assign a number to a cell value, do this:

oCell.Value = -12345678

karolus · September 2, 2025, 4:00pm

from com.sun.star.lang import Locale

def set_and_format_silly_strings(*_):
    loc = Locale(Country = "US",Language = "en")
    doc = XSCRIPTCONTEXT.getDocument()
    some_string = "(123456789)"  # it should become negative because (…) around ;-)
    val = float(some_string.replace("(","-").replace(")",""))
    target = doc.CurrentSelection
    target.Value = val
    formatnumber = doc.NumberFormats.queryKey("#,##0.00", loc, True)
    target.NumberFormat = formatnumber

Villeroy · September 2, 2025, 5:41pm

Quite often, this type of question arises from bad data import. Do you get “(12345678)” from csv import or from clipboard?

fpy · September 2, 2025, 8:44pm

daily : How can write whole date into cell and set CellContentType as value?

Zizi64 · September 3, 2025, 3:54am

I suggest you (again):
Use predefined Cell Styles (of a Templete) for the formatting cells by a code.

Lupp · September 2, 2025, 5:23pm

I don’t know how well you understand the different ways of number formatting and their relation to the conversion of numbers into text and reversely.

You shouldn’t need user code (macros) for either purpose.
Study the attached example, and come back (if needed) explaining your reasons for what you think to need macro programming.

disask_126129_TextToNumberFromAbsurdRepresentation.ods (29.8 KB)