Selecting the tenant SMTP address from get-mailbox's emailaddresses attribute

 When performing tenant to tenant migrations, it's critical to use their tenant address so that post vanity domain name removal you can still reference and access the source side tenant.  For some time I did this pretty manually, while knowing there had to be a better way.  So without much more chatter, here's how I do this in Excel now.

First, get-mailbox -resultsize unlimited | export-csv .\temp\allmailboxes.csv -nti

Then, open in Excel, format as a table, and add a column for tenantaddress

Within that column, here's the code you'd want:

Consider the randomness of emailaddresses with smtp, sip, x400/500,etc.  Many times I want a report of all of one kind of address or the alias of each domain, so I came up with this:

 

=MID([@emailaddresses],FIND("smtp:",[@emailaddresses])+5,(SEARCH("@tenantname.onmicrosoft.com",[@emailaddresses])-FIND("smtp:",[@emailaddresses])-5))

 

Things to update for your needs:

1. In Yellow, the address type of "smtp" means non primary.  FIND is case sensitive, so if you want only primary, you should use PrimarySMTPaddress instead of this.  If you want case insensitive, change FIND to SEARCH above.
2. In Blue, the domain name with the tenant address you are seeking
3. In Red, if you change from say SMTP: to X500: the 5's work.  If you do SIP: then you want a 4 as its one less character.
   

 

 

End result:

 

 

Since I know in my case I wanted their tenantname alias, I can re-concatenate that from here, or adjust my search to catch exactly the whole email, depending on your Excel skills.