Excel 2007 : Getaddress for multi cells problem

[mobik]

Hi there,
Can anyone please help me with a getaddress problem.
I have managed to create a macro for getaddress.

I have a column of URL linked email addresses.
They show as email
and I can use the =getaddress(b1) on it to extract and show the actual email address.

but I can't seem to get it working for the whole column ?!

Could someone please tell me how I code it or write it in the function bar ?

many thanks

Karen

ps I actually have Excel 2007 !

[abousetta]

Hi Karen and welcome to the forum,

Could you please explain what you mean by "I can't seem to get it working for the whole column". Could you post a sample workbook with the before and after (what you expect should happen)?

abousetta

[mobik]

Hi Aboussetta,

I think I've attached a pic of my spreadsheet.
hopefully it shows 'email' as a hyperlink.
The first one shows an actual email address as I can do the
=getaddress(e13) on it to extract the email address.
But as you see, there is a column of emails (a long column)
and I would like to do the getaddress statement to cover them all in one go.
I was trying =getaddress(e13:e99) but it didn't work!

Does this explain and show it ok ?

Thanks
Karen



emailxlspic.jpg

[abousetta]

Hi Karen,

You're right that =getaddress(e13:e99) won't work, but what you are trying to do exactly? Are you trying to put all the email addresses in one cell or do you just want them all? If its the latter then in just drag the cell with =getaddress(e13) and it will get put the email addressess in each respective row.

=getaddress(E13)&”,”&getaddress(E14)&”,”&getaddress(E15)&”,”&getaddress(E16)&”,”&getaddress(E17)&”,”&getaddress(E18)&”,”&getaddress(E19)&”,”&getaddress(E20)&”,”&getaddress(E21)&”,”&getaddress(E22)&”,”&getaddress(E23)&”,”&getaddress(E24)&”,”&getaddress(E25)&”,”&getaddress(E26)&”,”&getaddress(E27)&”,”&getaddress(E28)&”,”&getaddress(E29)&”,”&getaddress(E30)&”,”&getaddress(E31)&”,”&getaddress(E32)&”,”&getaddress(E33)&”,”&getaddress(E34)&”,”&getaddress(E35)&”,”&getaddress(E36)&”,”&getaddress(E37)&”,”&getaddress(E38)&”,”&getaddress(E39)&”,”&getaddress(E40)&”,”&getaddress(E41)&”,”&getaddress(E42)&”,”&getaddress(E43)&”,”&getaddress(E44)&”,”&getaddress(E45)&”,”&getaddress(E46)&”,”&getaddress(E47)&”,”&getaddress(E48)&”,”&getaddress(E49)&”,”&getaddress(E50)&”,”&getaddress(E51)&”,”&getaddress(E52)&”,”&getaddress(E53)&”,”&getaddress(E54)&”,”&getaddress(E55)&”,”&getaddress(E56)&”,”&getaddress(E57)&”,”&getaddress(E58)&”,”&getaddress(E59)&”,”&getaddress(E60)&”,”&getaddress(E61)&”,”&getaddress(E62)&”,”&getaddress(E63)&”,”&getaddress(E64)&”,”&getaddress(E65)&”,”&getaddress(E66)&”,”&getaddress(E67)&”,”&getaddress(E68)&”,”&getaddress(E69)&”,”&getaddress(E70)&”,”&getaddress(E71)&”,”&getaddress(E72)&”,”&getaddress(E73)&”,”&getaddress(E74)&”,”&getaddress(E75)&”,”&getaddress(E76)&”,”&getaddress(E77)&”,”&getaddress(E78)&”,”&getaddress(E79)&”,”&getaddress(E80)&”,”&getaddress(E81)&”,”&getaddress(E82)&”,”&getaddress(E83)&”,”&getaddress(E84)&”,”&getaddress(E85)&”,”&getaddress(E86)&”,”&getaddress(E87)&”,”&getaddress(E88)&”,”&getaddress(E89)&”,”&getaddress(E90)&”,”&getaddress(E91)&”,”&getaddress(E92)&”,”&getaddress(E93)&”,”&getaddress(E94)&”,”&getaddress(E95)&”,”&getaddress(E96)&”,”&getaddress(E97)&”,”&getaddress(E98)&”,”&getaddress(E99)

That's assuming that I understand what the UDF getaddress does.

abousetta

P.S. If it doesn't work then I need to see a sample workbook.

[mobik]

Hi Abousetta,

Firstly, thank you for trying.
Secondly I've attached a larger pic of the worksheet if that helps....
but anyway, maybe to expand more :-

Each of the email hyperlinks contain a different email address. They are for clubs in London.... and I want the email address for each of those clubs.
I have been given that table that I put onto the spreadsheet, but where most people just want one email address, its fine for them to have it 'hidden' !

By typing in the bar - =getaddress(E117) it displayed the email address that 'email' in that cell was hyperlinked to.

but I have a lot of 'email' s in the E column so I would like to find a formula to type in to get each and every
'email' hyperlink displayed as its actual email address.

Sorry, you seem to know what you're talking about, I just don't know the lingo well enough to explain myself and don't know how to show it any better ?

From your little code picture - you showed repeating the getaddress statement for each cell, but I have over 100 of them!
and as I don't know where or how you created that statement, it looks a bit frightening to me lol

Does this make it any clearer?

thanks

Karen

emailxlspic2.jpg

[abousetta]

Hi Karen,

I am assuming that you are using a UserDefined Function like this one from OzGrid.com:

Function GetAddress(HyperlinkCell As Range)
    GetAddress = Replace(HyperlinkCell.Hyperlinks(1).Address, "mailto:", "")
End Function

So let's try something step by step. I want you to put in a cell the following formula:

=getaddress(E13) and click Enter

Now go to the lower right-hand corner of the this cell. The cursor should turn into a plus sign (+). Left click with the mouse and while holding down, drag down until the desired row (same column).

Other option would be to copy the cell that contains the formula and then click on the next cell below it, and while you press on the shift key use the arrows to move down to the last row with emails. The area should all be highlighted. Then press Ctrl + V (paste). The formula should be pasted and updated to reflect the respective row numbers.

Let me know if this works.

abousetta

[mobik]

Hi Abousetta

That was close and you've got the comment right,
but both methods you suggested resulted in the 1st target email address showing up in all instances!

The 2nd method, doing the copy and paste was interesting because having converted the 1st 'email'
and ctrl C shift move down - resulted in all cells displaying the same email address BUT each cell
showing what appeared to be the correct formula as in
=getaddress(e15)
=getaddress(e16)
=getaddress(e17)
=getaddress(e18)

so it looks like it copies the target email address, then creates the cell forumla ??? or something like that ????!!!!!

any more suggestions for me ??

thank you

Karen

[abousetta]

Karen,

I really need to see a sample of dummy data. You can change the names, etc. but I need to see why its not working on your sheet. You can either upload a sample workbook or PM me and I can send you my email address. Without any further details, I don't see how I can assist any further.

abousetta

[mobik]

HI Abousetta,
Thank you
How can I upload or send the file then ?
See - I don't even know how to post properly !!!

Karen

[abousetta]

If you want to upload, you can by clicking on "Go Advanced", scroll down to the "Manage Attachments" --> Basic --> upload the file Or you can PM me and I will send you my email address.

abousetta

[mobik]

Hi Abousetta

I think I've managed to upload it !!??

Karen

ps please forgive the state of the file - I add bits in from all sorts of places and don't
know how to put them right!!!

[abousetta]

Formula worked fine for me. Dragged and it gave the right results. Check that calculation are on Automatic not Manual.

abousetta

[mobik]

and you got a different email address for each 'email' ?
oh dear, I'm doing something wrong then!
Yes calculation is on auto...
I'm setting up the first get address and pressing enter.
clicking on that same field I've just got address for,
ctrl c
shift and dragged down to bottom of 'email' s I'm after
releasing drag
ctrl v
and I get the same email address in each field !

so where am I going wrong?

I'm sorry if I appear thick
and I do appreciate your help
If I can get this, It will save me hours!

thanks
Karen

[mobik]

Mr Abousetta,

You are a star.

Top ratings.

I WAS doing something a bit wrong,
but
by your first comments I knew it was possible
and following your skype chat and show

I NOW KNOW how to do it.

THank you thank you thank you.

and a happy new year to you.

best regards

Karen

and when I went back to this forum it didn't allow me to rate you, so my comments here show it
but if I get back and can do so = I will.

star star star star star