Return text based on differing size "prefixes" found in ajacent text

[user69]

Hi,

Hope you might be able to help with this one. The formula I need to come up with is for Column E.

Column A and B give a list of all prefixes and their corresponding work type. This could be considered a 'legend' for the formula to lookup from.

Column D is freehand manually typed text, where each line represents a call taken by call centre staff. If the call is classifiable into a type from column B, they put the corresponding prefix before their description of the call (i.e. from column A). If the call type is not any type in column B, they ignore the prefix when typing a description.

Each cell in Column E needs to look at the adjacent cell in Column D and determine if any prefixes from Column A are used.

If yes, return the text description (i.e. What the prefix stands for).
If no, return "Other".

The formula needs to find a perfect match in the initial text from Column D. If any text is accidently used before the prefix, or the prefix is misspelled, return "Other".

Hope I've explained it well enough. The tricky part for me is picking up different sized prefixes (which may have spaces). Screenshot below:

 

[Yongle]

You could avoid typo error
- insert a column for the prefix where user selects from a dropdown
- the formula that you need then becomes a simple VLLOOKUP

 

[sandy666]

Is that will be ok for you?

Prefix	Prefix Call Type		User text		User text	Call Type
HB P	Hamburger pickup		HB P for harry		BCIOP at closing time	Boss checking in on performance
HB D	Hamburger delivery		SWEPS D for footy team		HB D	Hamburger delivery
P D	Pie delivery		esjdenrd hdhrdh		HB P for harry	Hamburger pickup
PACD	Pie and chips delivery		C D no name		PACP	Pie and chips pickup
PACP	Pie and chips pickup		TM call centre (ignored!!)		PACP	Chips pickup
CD	Chips delivery		C D for sara and john		PC 1 blocked the number...	Prank call
CP	Chips pickup		SWEPS D for ballet team		SWEPS D for ballet team	Sundae with extra pink sprinkles delivery
SWEPS D	Sundae with extra pink sprinkles delivery		SPAM SPAM SPAM		SWEPS D for footy team	Sundae with extra pink sprinkles delivery
PC	Prank call		HB D		TM call centre (ignored!!)	Telemarketer
TM	Telemarketer		1$$$%32523123123
BCIOP	Boss checking in on performance		PC 1 blocked the number...
			BCIOP at closing time
			blah blah blah
			PACP
			&&&&&&&&
			PAC_D

it will eliminate text which doesn't contain any prefix in user text (in short: no Other)

 

[sandy666]

green table should be like this:

User text	Call Type
BCIOP at closing time	Boss checking in on performance
HB D	Hamburger delivery
HB P for harry	Hamburger pickup
PACP	Pie and chips pickup
PC 1 blocked the number...	Prank call
SWEPS D for ballet team	Sundae with extra pink sprinkles delivery
SWEPS D for footy team	Sundae with extra pink sprinkles delivery
TM call centre (ignored!!)	Telemarketer

but giving the user the option to enter what he wants is a bad idea, so I think the Yongle option is a better solution