At 21:06 08/11/2007 -0800, Allen Schaaf wrote:
Brian Barker wrote:
At 18:03 06/11/2007 -0800, Allen Schaaf wrote:
I inherited an .ods spreadsheet that has hyperlinks to an e-mail
address. About 10,000 of them.
The text says "Email Me" but when you look at the hyperlink it
says "mailto:[EMAIL PROTECTED]".
I want to create a column next to it that has just the email
address without having to collect them one by one so that I can
send out group emails to a few people at a time when a subject
comes up that would interest those people.
So how do I de-link them in bulk, not 10,000 one at a time?
See the function ExtractHyperLink at:
http://www.oooforum.org/forum/viewtopic.phtml?p=80625#80625
or the function CELL_URL at:
http://www.oooforum.org/forum/viewtopic.phtml?t=32909 .
I trust this helps.
What NoOP suggested doesn't work like Michele said.
The problem now is I have no (not even one sliver of one) clue of
how to turn those code snippets into functions that I can use.
Pointers, please?
To install the code:
o Go to Tools | Macros > | Organize Macros > | OpenOffice.org Basic... .
o Select My Macros and New.
o Paste the function text from the first link above into the macro
code window that appears. (Use the "function ExtractHyperLink" from
the last entry on that page.) You can add it to whatever you already
have in the window or just replace the empty Sub that you will see there.
o Close the code window.
To use the function:
The function needs the sheet, column, and row of the cell to be
specified separately, so you need to spell those out. Suppose that
your existing hyperlinks are in column A. In row 1 of a new column, enter:
=EXTRACTHYPERLINK(SHEET(A1);COLUMN(A1);ROW(A1))
Drag the fill handle down the new column to copy this formula (as
modified) into the rest of the column. You should see something of
the form "mailto:[EMAIL PROTECTED]" in the new column. If you don't
need the "mailto:"; part, use this formula instead:
=MID(EXTRACTHYPERLINK(SHEET(A1);COLUMN(A1);ROW(A1));8;100)
This takes the part of the earlier string from the eighth character
onward (for up to 100 characters): in other words, it loses the first
seven characters, which should be the "mailto:"; part.
If you prefer to have the values back in the original column, beware
of pasting them back, as this will destroy the values on which the
function is operating. Instead, copy the new column and then use
Edit | Paste Special... (or right-click | Paste Special... or
Ctrl+Shift+V). In the Paste Special dialogue box, remove the tick
from "Paste all" and make sure that Formulas is *not* ticked. This
way, you will be pasting the results of the formulae rather than the
formulae themselves.
If you disturb the new values, Calc may turn them into hyperlinks
again (unless you turn off this facility), but at least the text in
the cell will now be the e-mail address that you want and not the
original "Email Me". You can reset this anyway using Format |
Default Formatting.
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
