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 !
Last edited by mobik; 12-28-2011 at 10:37 PM. Reason: letting people know excel version
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
Please consider:
Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.
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
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.
That's assuming that I understand what the UDF getaddress does.=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)
abousetta
P.S. If it doesn't work then I need to see a sample workbook.
Please consider:
Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.
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
Hi Karen,
I am assuming that you are using a UserDefined Function like this one from OzGrid.com:
So let's try something step by step. I want you to put in a cell the following formula:Function GetAddress(HyperlinkCell As Range) GetAddress = Replace(HyperlinkCell.Hyperlinks(1).Address, "mailto:", "") End Function
=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
Last edited by abousetta; 12-28-2011 at 06:42 PM.
Please consider:
Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.
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
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
Please consider:
Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.
HI Abousetta,
Thank you
How can I upload or send the file then ?
See - I don't even know how to post properly !!!
Karen
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
Please consider:
Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.
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!!!
Last edited by mobik; 12-28-2011 at 08:39 PM.
Formula worked fine for me. Dragged and it gave the right results. Check that calculation are on Automatic not Manual.
abousetta
Please consider:
Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.
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
Re: Getaddress for multi cells problem
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks