tradeaccepted
New Member
- Joined
- Jun 11, 2013
- Messages
- 33
I have a sheet that contains Email metadata. The data looks like this:
https://ethercalc.org/houk0a1zt3
The end goal is to take an email header that looks like: LASTNAME, FIRSTNAME[FIRST.LAST@example.com]
And convert it to only: LASTNAME, FIRSTNAME.
Problem is that there are many different formatting variations for email headers. You can view some of them in the EtherCalc link above.
The one thing that is absolute, is that each different recipient is separated by a semicolon.
I made a pretty simple Macro to complete this. After the below macro is run, you will see the results that are in Column B. This does handle most cases where I need to normalize an email address. I am running into a problem when there is no name for the email.
For example, if the address looked like this:[FIRST.LAST@example.com];[FIRST.LAST@example.com]
The VBA will return: ;
It will delete both email addresses, because that's exactly what the VBA tells it to do. If the email header looks like the above example, I would just want to return the actual email address without surrounding <>, () or [].
Has anyone come across this task or could think of a better way to complete this? Willing to abandon the entire VBA if someone thinks of a better way.
As always, much appreciated for taking the time to read my request.
https://ethercalc.org/houk0a1zt3
The end goal is to take an email header that looks like: LASTNAME, FIRSTNAME[FIRST.LAST@example.com]
And convert it to only: LASTNAME, FIRSTNAME.
Problem is that there are many different formatting variations for email headers. You can view some of them in the EtherCalc link above.
The one thing that is absolute, is that each different recipient is separated by a semicolon.
I made a pretty simple Macro to complete this. After the below macro is run, you will see the results that are in Column B. This does handle most cases where I need to normalize an email address. I am running into a problem when there is no name for the email.
For example, if the address looked like this:[FIRST.LAST@example.com];[FIRST.LAST@example.com]
The VBA will return: ;
It will delete both email addresses, because that's exactly what the VBA tells it to do. If the email header looks like the above example, I would just want to return the actual email address without surrounding <>, () or [].
Has anyone come across this task or could think of a better way to complete this? Willing to abandon the entire VBA if someone thinks of a better way.
Code:
Sub normalize_emails_no_text_to_columns()
'
' normalize_emails_no_text_to_columns Macro
'
' This will remove all email addresses from column B that are surrounded by <>, () or [] from the sheet, leaving only names.
Columns("B:B").Select
Selection.Replace what:="<*>", replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("B:B").Select
Selection.Replace what:="(*)", replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("B:B").Select
Selection.Replace what:="
[*]", replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
' This will remove all of the (")s
Columns("B:B").Select
Selection.Replace what:="""", replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
' This will replace all ; ; with a single ;
Columns("B:B").Select
Selection.Replace what:="; ;", replacement:=";", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
' This will expand the columns to be readable
Cells.Select
Cells.EntireColumn.AutoFit
End SubAs always, much appreciated for taking the time to read my request.
