URL or hyperlink to text

Is there a MACRO to convert a URL or hyperlink to text;

EG:
Jim (Hyperlink) convert it to; jim@bookman-jim.biz

Open office had one, but it is now lost and I have deleted Open Office in favor of LibreOffice.

Thanks,
⌡im [THE ßookMan]
92Ø.265.5966

See another similar question.

No luck!

Tried all 3 formulas in A2 … nothing happened.

Have screen shot to share … HOW?
Will send sheet … How?

⌡im [THE ßookMan]
92Ø.265.5966

Before we proceed with your troubleshooting, please can you claime that this is not a spam database? I would hate to know that by helping one person, I have caused inconvenience to a large number of people.

Try this:

Function getURL(sCellAddress As String) As String
Dim oCell As Variant
Dim oTextFields As Variant
Dim aTemp As Variant 
On Error GoTo wrong_result
  getURL = "Wrong address"
  oCell = ThisComponent.getSheets().getCellRangesByName(sCellAddress)(0).getCellByPosition(0, 0)
  oTextFields = oCell.getTextFields()
	If oTextFields.getCount() > 0 Then 
		getURL = oTextFields.getByIndex(0).URL
	Else
		If InStr(oCell.getFormula(),"HYPERLINK") > 0 Then
			aTemp = Split(oCell.getFormula(),"""")
			If UBound(aTemp) > 1 Then getURL = aTemp(1)
		EndIf 
	EndIf 
wrong_result:
End Function

You can call it like as =GETURL(CELL("ADDRESS";A2))

or =GETURL("$Sheet1.A2")

or =GETURL("$Sheet1.A" & ROW())

Update
image description

Ran perfectly the first time.
Cleared data, area, added another list, repeated as the first time … NOTHING???

Please help!
Tried to U/L CALC that won’t run, but no luck.

Thanks
⌡im [THE ßookMan]
92Ø.265.5966

NOTHING???

I’m sure NOTHING is an accurate description of what happened. ANYTHING was bound to happen. For example, the inscription “Wrong address” instead of addresses or any other

NOTHING !

It’s like the MACRO wasn’t even there (it is, just the same as the one listed above).
No error message, highlight doesn’t move, not even a blink when I try to run it.

tried deleting the MACRO and reloading it, b ut that did nothing either.’

Loaded a fresh instance of the latest JAVA,
Itjust sits there …

How to get a copy to you?

⌡im [THE ßookMan]
92Ø.265.5966

when I try to run it

WAIT! Are you trying to RUN this function? It doesn’t need to run!

Just write the formula in a blank cell, near the hyperlinked cell. CALC will run the macro itself and place the result in a cell with a formula

Nope!
Have done that several times.
Also wrote the formula in a brand new CALC sheet … NADA???

How to get the CALC file to you?

⌡im [THE ßookMan]
92Ø.265.5966

Jon Sum,
Thanks for the MACRO, but it didn’t work for me.
I must be doing something wrong.

Open “Get URL”
then
Load Hyperlinks into Col A1 – A???
Highlight Hyperlinks
TOOLS → MACROS → Organize → BASIC → Get URL
.

This is the macro:
.
Function getURL(sCellAddress As String) As String
Dim oCell As Variant
Dim oTextFields As Variant
Dim aTemp As Variant
On Error GoTo wrong_result
getURL = “Wrong address”
oCell = ThisComponent.getSheets().getCellRangesByName(sCellAddress)(0).getCellByPosition(0, 0)
oTextFields = oCell.getTextFields()
If oTextFields.getCount() > 0 Then
getURL = oTextFields.getByIndex(0).URL
Else
If InStr(oCell.getFormula(),“HYPERLINK”) > 0 Then
aTemp = Split(oCell.getFormula(),"""")
If UBound(aTemp) > 1 Then getURL = aTemp(1)
EndIf
EndIf
wrong_result:
End Function

.
The MACRO is there as you wrote it, but nothing happens?
C:\fakepath\Get URL (LO).ods

Is there no one that can help?

I have tried many things as prescribed above, but this CALC formula just won’t run and convert links to text.
The first time I tried, it converted 100+ links to text in a flash, but since then NOTHING!!!

I have 100’s more to convert.

PLEASE HELP!
I would be happy to have someone remote into mu machine or I can U/L the sheet to your E-mail (I’ll lose it immediately after)

Thanks,
⌡im [THE ßookMan]
92Ø.265.5966

Worked GREAT!
Thanks a bunch,
⌡im [THE ßookMan]
92Ø.265.5966

Premature post … Ignore.