• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Change lotus to OutLook

Monty

Well-Known Member
Hello Everyone..

Quite interesting question....When we are working with lotus notes had email ids like Monty Ronnie/Had/Asia/,...Of course depending on region email id is different....Now the question is how can copy 1000 + Email ids and paste in excel and change them to OutLook email ids....Like Monty Ronnie;

Process would let's manually copy ids from lotus notes then paste them to excel...Now macro should covert them to our Requirements.

Hope not confused..
 
Here is the attachment with sample email ids:
 

Attachments

  • Lotus to Outlook email ids.xlsx
    8.1 KB · Views: 4
Maybe you can use UDF like one below and then use it like below:
=ConvertLNToOL(A1)
Code:
Public Function ConvertLNToOL(rng As Range)
Dim varOut
Dim i As Long
varOut = Split(Trim(rng.Value), ",")
For i = LBound(varOut) To UBound(varOut)
  If Len(Trim(varOut(i))) > 0 Then varOut(i) = Mid(varOut(i), 1, InStr(1, varOut(i), "/", vbTextCompare) - 1)
Next i
ConvertLNToOL = Join(varOut, ";")
ConvertLNToOL = Left(ConvertLNToOL, Len(ConvertLNToOL) - 1)
End Function
 
Thank you so very much..Working fantastic.

Modified as per my requirement.

Code:
Dim ConvertLNToOL()
Dim varOut
Dim i As Long
set Rng=Range("A1").value
varOut = Split(Trim(rng.Value), ",")
For i = LBound(varOut) To UBound(varOut)
  If Len(Trim(varOut(i))) > 0 Then varOut(i) = Mid(varOut(i), 1, InStr(1, varOut(i), "/", vbTextCompare) - 1)
Next i
ConvertLNToOL = Join(varOut, ";")
ConvertLNToOL = Left(ConvertLNToOL, Len(ConvertLNToOL) - 1)
End Sub
 
Back
Top