How to import MS Excel spreadsheet with links?

[tichyb]

When I open the xlsx file (or convert it to ods file) then the links within the spreadsheet do not work.
The reason is that the format of Excel link starts with [#] whereas the one of Libre Office starts just with the # without []. Likewise with the separator ! and . between the Sheet name and Cell(s) name in the link.
It is possible to edit the link by manual erasing the [ and ] characters. Then the link starts working even without replacing the exclamation mark by the dot. That is possible for a few links but not for hundreds.
When I use the Search/Replace [#] to # then the link is corrected, however the displayed text in the cells disappears.
In this case replacing the ! by . is also necessary. Batch replacing leads to the correct functionality of the links but also modifies the text strings where the ! marks were used as linguistic exclamation.

Is there any way how to batch correct the references in the spreadsheet when the source is xlsx and target ods?
Why the conversion procedure between xlsx and ods does not the appropriate modification of the link format?

[Lupp]

Sorry! Don’t understand.

1. Assumed “the links within …” refer to cells or to cell ranges: Calc should be able to open XLSX and to convert it automatically to its own standards. What did hinder this in your case?

2. Why do you get references “within the spreadsheet” using the full syntax of references into other files?

3. May you refer to something like (cell or range) ADDRESSES given as text for some reason?

4. Please QUOTE one of the cell contents you complain about.

[tichyb]

Here is an example.
When I open the xlsx file then the link expression in the cell looks like this:
=HYPERLINK("[#]Attributes!B4:D4";“Material Type”)
where Attributes is the name of another sheet in the document and Material Type is the text displayed in the cell.

I think that it should read like this:
=HYPERLINK("#Attributes.B4:D4";“Material Type”)
I.e. without [] and . instead of ! mark. At least when I edit the expression to this form then it works as needed.

[Lupp]

‘Find & Replace’, ‘Regular expressions’ enabled, ‘Search in’ ‘Formulae’

‘Search for’

\[#\]([^!]+)!

‘Replace with’

#$1.

See also attached image! FindReplaceInternalHyperlinkExcelToCalc.png

(Here are to take EXACTLY the characters as given in the dedicated lines. These Including ! and . Regard this dot after $1)

[begin EDIT]

I First couldn’t get the backslashes correctly displayed. Hope it’s ok now.[end EDIT]