Compare strings with substrings in excel

aspire

New Member
Joined
May 30, 2017
Messages
2
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.



[TABLE="width: 443"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Mail subject[/TD]
[TD]Substring[/TD]
[TD]Action[/TD]
[/TR]
[TR]
[TD]ABC_DEF_GHJ:12345[/TD]
[TD]ABC_DEF_GHJ[/TD]
[TD]Reply[/TD]
[/TR]
[TR]
[TD]ABC_DEF_GHJ:12346[/TD]
[TD]ABC_DEF_GHJ[/TD]
[TD]Reply[/TD]
[/TR]
[TR]
[TD]ABC_DEF_GHJ:12347[/TD]
[TD]ABC_DEF_GHJ[/TD]
[TD]Reply[/TD]
[/TR]
[TR]
[TD]ABC_DEF_GHJ:12348[/TD]
[TD]ABC_DEF_GHJ[/TD]
[TD]Reply[/TD]
[/TR]
[TR]
[TD]ABC_DEF_GHJ:12349[/TD]
[TD]ABC_DEF_GHJ[/TD]
[TD]Reply[/TD]
[/TR]
[TR]
[TD]RST_UVW_XYZ:45678[/TD]
[TD]RST_UVW_XYZ[/TD]
[TD]Forward[/TD]
[/TR]
[TR]
[TD]RST_UVW_XYZ:45679[/TD]
[TD]RST_UVW_XYZ[/TD]
[TD]Forward[/TD]
[/TR]
[TR]
[TD]RST_UVW_XYZ:45680[/TD]
[TD]RST_UVW_XYZ[/TD]
[TD]Forward[/TD]
[/TR]
[TR]
[TD]RST_UVW_XYZ:45681[/TD]
[TD]RST_UVW_XYZ[/TD]
[TD]Forward[/TD]
[/TR]
</tbody>[/TABLE]


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

Substring Action
[TABLE="width: 443"]
<tbody>[TR]
[TD]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

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Also please note that there could be variable part before the fixed part as well say like below

[TABLE="class: cms_table, width: 443"]
<tbody>[TR]
[TD] Mail subject[/TD]
[TD]Substring[/TD]
[TD]Action[/TD]
[/TR]
[TR]
[TD]Urgent ABC_DEF_GHJ:12345[/TD]
[TD]ABC_DEF_GHJ[/TD]
[TD]Reply[/TD]
[/TR]
[TR]
[TD]Low ABC_DEF_GHJ:12346[/TD]
[TD]ABC_DEF_GHJ[/TD]
[TD]Reply[/TD]
[/TR]
[TR]
[TD]Priority ABC_DEF_GHJ:12347[/TD]
[TD]ABC_DEF_GHJ[/TD]
[TD]Reply[/TD]
[/TR]
[TR]
[TD]Urgent ABC_DEF_GHJ:12348[/TD]
[TD]ABC_DEF_GHJ[/TD]
[TD]Reply[/TD]
[/TR]
[TR]
[TD]Low ABC_DEF_GHJ:12349[/TD]
[TD]ABC_DEF_GHJ[/TD]
[TD]Reply[/TD]
[/TR]
[TR]
[TD]Priority RST_UVW_XYZ:45678[/TD]
[TD]RST_UVW_XYZ[/TD]
[TD]Forward[/TD]
[/TR]
[TR]
[TD]RST_UVW_XYZ:45679[/TD]
[TD]RST_UVW_XYZ[/TD]
[TD]Forward[/TD]
[/TR]
[TR]
[TD]RST_UVW_XYZ:45680[/TD]
[TD]RST_UVW_XYZ[/TD]
[TD]Forward[/TD]
[/TR]
[TR]
[TD]RST_UVW_XYZ:45681[/TD]
[TD]RST_UVW_XYZ[/TD]
[TD]Forward[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,889
Messages
6,175,224
Members
452,620
Latest member
dsubash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top