How can remove the leading apostrophe with OOBasic code?

English
,
#1

A leading apostrophe signifies that the cell should be treated as text in Libreoffice (same as in Excel).The apostrophe can only be seen in the Formula bar when selecting the cell, and otherwise stays invisible.
Create a new libreoffice file and type '123456 in A1.

The leading apostrophe can be removed manually with Find and Replace method.
I want to delete it with macro code:

Sub ReadNumber()
    Dim oSheet As Object
    Dim oCell As Object
    oSheet = ThisComponent.Sheets.getByName("Sheet1")
    oCell = oSheet.getCellByPosition(0, 0)
    msgbox oCell.string
end sub

123456 will be shown in message box ,instead of '123456.It can’t be read ,how can remove it with macro code then?

#2

We discussed a similar issue.
When you type '123456 into a cell, the cell value becomes the string 123456 (without the apostrophe). You see this in the output of the Msgbox function.
If you go into cell editing mode and remove the apostrophe (which indicates that the cell contains text and not a number), the cell value will change to the number 123456.

2 Likes
#4
  • Select the cell range(s) in question
  • menu:Edit>Find>Replace…
  • under More Options… check “Current selection” and “Regular Expression”
  • Search for: .+
  • Replace with: &
  • Replace all
1 Like
#3

This is wrong. The apposrophe is NOT part of the content. =LEN(A1) in a different cell will return 6 for your example and F&R doesn’t find it.

To do the trick manually you can use F&R with regular expressions. This proceeding can also be left to user code.

Suppressing number recognition (what’s done for the afflicted cells) is rarely a good idea.

A solution for your task by user code is contained and exemplified in the attached Calc file.
disask_126199_ActivateNumberRecognition.ods (13.8 KB)

#5

In your macro:

.SearchString = “.*”
.ReplaceString = “&”

In regular expression , “.” match any character, the asterisk * is a quantifier that means “zero or more occurrences” of the preceding element.

1.Why does “&” mean here?
2.replace any character with “&”?

I am confused!

#6

The search string ".*" simply finds the complete text content of the respective cell.
The replacement string "&" works as a special command to insert everything that was found.
Since the apostrophe is NOT content of the cell the newly inserted string can trigger a new number recognition if not the cell is explicitly set to NF "text" (code @) in a different way.

BTW:
You should also consider to tell if the suggested solution worked for you as expected.

#7

Yes it work as my desired,but the principle behind is so complicated.
In my case , '.’ matches '123456.
& is special ,no meta-charcater such as “&” in other program language , it is only work in OOBasic ?
Please give more info on “&” in OOBasic.
Normal meta-character in regular expression grammar:
^,$ ,、+、?、{n}、{n,}、{n,m}.\ .

#8

https://help.libreoffice.org/25.8/en-US/text/shared/01/02100001.html?&DbPAR=WRITER&System=UNIX
& or $0 replaces anything that has been matched by the search expression. This does the same as re-typing the cell content.

#9

… and is completly unrelated to BASIC, its just a reference to the whole thing found by »search-expression«

#10

& can filter the leading apostrophe ,keep any characters behind the leading apostrophe ,it is designed for this purpose.

#11

This is wrong.
You need to distinguish the search term
.* (whatt I actually used form
. (wgat you repeat after my first statement in the case.
You claim again that the match start with an apostrophy. It doesn’t.

That the replace code
&
does NOT contain the a ' as marker is essential for the working.

The principle is simply to insert the numeric match without the marker for “Don’t try a recognition.” This way a recognition process is triggered.
Everything was told before.
That the code is a bit more complicated than you like it, is due to the fact that it regards the current selection. To do the thing for a singtle cell manually is so simple that writing code for it is wasting time.
Even for any selection the manual usage of F&R is simple, but you explicitly ,wanted user code.

#12

That’s wrong again. Nothing is filtered by using & . The apostrophe simply is not part of the textual content of the cell in the discussed situation.
(Was emphasized before.)

Your orginal attempt was

Sub ReadNumber()
    Dim oSheet As Object
    Dim oCell As Object
    oSheet = ThisComponent.Sheets.getByName("Sheet1")
    oCell = oSheet.getCellByPosition(0, 0)
    msgbox oCell.string
end sub

What only works for the first cell of the sheet named “Sheet1”.
Only for learning you may want to get an equally specialized code.
If the cell is assured to show the tickmark during editing and only during editing, you can use:

Sub recognizeNumber() REM Works only for cell $Sheet1.$A$1
    Dim oSheet As Object
    Dim oCell As Object
    oSheet        = ThisComponent.Sheets.getByName("Sheet1")
    oCell         = oSheet.getCellByPosition(0, 0)
    oCell.Formula = oCell.String
End Sub

This may look simple, but may not easily be understood.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.