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
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
1 Like
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.