Compare strings with substrings in excel

[aspire]

Hi,

I have certain mail subjects which are part static part dynamic for e.g. Job ABC_DEF_GHJ:12345 Here the subject preceding the : is fixed, while the one after is dynamic.

In a day I will have 100's of such mail subjects, some of them will have common fixed parts, only the dynamic vary, But the actions on these mails with the same fixed part is common.

Mail subject	Substring	Action
ABC_DEF_GHJ:12345	ABC_DEF_GHJ	Reply
ABC_DEF_GHJ:12346	ABC_DEF_GHJ	Reply
ABC_DEF_GHJ:12347	ABC_DEF_GHJ	Reply
ABC_DEF_GHJ:12348	ABC_DEF_GHJ	Reply
ABC_DEF_GHJ:12349	ABC_DEF_GHJ	Reply
RST_UVW_XYZ:45678	RST_UVW_XYZ	Forward
RST_UVW_XYZ:45679	RST_UVW_XYZ	Forward
RST_UVW_XYZ:45680	RST_UVW_XYZ	Forward
RST_UVW_XYZ:45681	RST_UVW_XYZ	Forward

<colgroup><col><col><col></colgroup><tbody>
</tbody>

So in essence if I create an Excel database containing Substring and action I will have something like below

Substring Action

ABC_DEF_GHJ Reply RST_UVW_XYZ Forward Now assuming I have all the mail subjects with me in an excel, how can I compare it against the Excel database that I created above to get the action against it. Please suggest. Best Regards Shailesh

<tbody>
</tbody>

 

[aspire]

Also please note that there could be variable part before the fixed part as well say like below

Mail subject	Substring	Action
Urgent ABC_DEF_GHJ:12345	ABC_DEF_GHJ	Reply
Low ABC_DEF_GHJ:12346	ABC_DEF_GHJ	Reply
Priority ABC_DEF_GHJ:12347	ABC_DEF_GHJ	Reply
Urgent ABC_DEF_GHJ:12348	ABC_DEF_GHJ	Reply
Low ABC_DEF_GHJ:12349	ABC_DEF_GHJ	Reply
Priority RST_UVW_XYZ:45678	RST_UVW_XYZ	Forward
RST_UVW_XYZ:45679	RST_UVW_XYZ	Forward
RST_UVW_XYZ:45680	RST_UVW_XYZ	Forward
RST_UVW_XYZ:45681	RST_UVW_XYZ	Forward

<tbody>
</tbody>

 

[gromexe]

So you already created a separate table like above? The Substring, and Action columns are already in separate columns?

For your question, you said:

"assuming I have all the mail subjects with me in an excel"

Do you have the first column "Mail Subject" only? And that's all?

And you want to separate the Subject and Action from the entire string?

Maybe post what you have, and what you expect to result. It seems like you're asking for something that is simple to achieve. I'm just not sure exactly what you're trying to do.