Brian Barker wrote:
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.
Both worked just fine so that problem is solved.
However, I went to try the second of the links, did the same
actions and went to "Save As" and it was grayed out, so I just a
did a "Save" and it wiped out what I had already. So the question
is, how do I save them as separate macros?
Thanks,
Allen
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
