Skip to main content
Topics#Data ManagementGlenn Daly 的问题
Glenn Daly (Pod Point) 提问于 #Data Management
2017年3月14日 11:32

Strip out unnecessary characters from a string to display only the To: email address

We have a field named BMCServiceDesk__EmailConversationData__c that stores the email addresses from the header of incoming emails. This happens like below.

 

From:giovanni.sorenti@jisc.ac.uk#@#@#@#.To:david.patterson@jisc.ac.uk#@#@#@#.Cc:complaints.team@jisc.ac.uk;service@ja.net

 

Or if there is no CC it looks something like this

 

From:ben.chapman@jisc.ac.uk#@#@#@#.To:service@ja.net#@#@#@#.Cc:

 

I want to be able to store the To: address in a new field called To__c.

 

For the above record I would like the To__c field to store "service@ja.net"

 

How do I do this, please see my attempt at a formula below

 

SUBSTITUTE( 

 

SUBSTITUTE( 

 

BMCServiceDesk__EmailConversationData__c, 

 

LEFT( 

 

BMCServiceDesk__EmailConversationData__c, 

 

FIND( 

 

"#@#@#@#.To:", 

 

BMCServiceDesk__EmailConversationData__c)), NULL) 

 

, 

 

RIGHT( 

 

BMCServiceDesk__EmailConversationData__c, 

 

FIND( 

 

"#@#@#@#.Cc:", 

 

BMCServiceDesk__EmailConversationData__c)), NULL)
2 个回答
  1. Sunil Sarilla (M&G Investments)
    2017年3月14日 11:56
    Hi Glenn,

     

    Please try the below

    LEFT(
    MID(
    BMCServiceDesk__EmailConversationData__c, FIND("To:",BMCServiceDesk__EmailConversationData__c)+3, 255
    ), 
    FIND("#", MID(BMCServiceDesk__EmailConversationData__c, FIND("To:",BMCServiceDesk__EmailConversationData__c)+3, 255))-1
    )

     

     
撰写回答...
0/9000