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?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Prefix[/td][td=bgcolor:#5B9BD5]Prefix Call Type[/td][td][/td][td=bgcolor:#5B9BD5]User text[/td][td][/td][td=bgcolor:#70AD47]User text[/td][td=bgcolor:#70AD47]Call Type[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]HB P[/td][td=bgcolor:#DDEBF7]Hamburger pickup[/td][td][/td][td=bgcolor:#DDEBF7]HB P for harry[/td][td][/td][td=bgcolor:#E2EFDA]BCIOP at closing time[/td][td=bgcolor:#E2EFDA]Boss checking in on performance[/td][/tr]

[tr=bgcolor:#FFFFFF][td]HB D[/td][td]Hamburger delivery[/td][td][/td][td]SWEPS D for footy team[/td][td][/td][td]HB D [/td][td]Hamburger delivery[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]P D[/td][td=bgcolor:#DDEBF7]Pie delivery[/td][td][/td][td=bgcolor:#DDEBF7]esjdenrd hdhrdh[/td][td][/td][td=bgcolor:#E2EFDA]HB P for harry[/td][td=bgcolor:#E2EFDA]Hamburger pickup[/td][/tr]

[tr=bgcolor:#FFFFFF][td]PACD[/td][td]Pie and chips delivery[/td][td][/td][td]C D no name[/td][td][/td][td]PACP[/td][td]Pie and chips pickup[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]PACP[/td][td=bgcolor:#DDEBF7]Pie and chips pickup[/td][td][/td][td=bgcolor:#DDEBF7]TM call centre (ignored!!)[/td][td][/td][td=bgcolor:#E2EFDA]PACP[/td][td=bgcolor:#E2EFDA]Chips pickup[/td][/tr]

[tr=bgcolor:#FFFFFF][td]CD[/td][td]Chips delivery[/td][td][/td][td]C D for sara and john[/td][td][/td][td]PC 1 blocked the number...[/td][td]Prank call[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]CP[/td][td=bgcolor:#DDEBF7]Chips pickup[/td][td][/td][td=bgcolor:#DDEBF7]SWEPS D for ballet team[/td][td][/td][td=bgcolor:#E2EFDA]SWEPS D for ballet team[/td][td=bgcolor:#E2EFDA]Sundae with extra pink sprinkles delivery[/td][/tr]

[tr=bgcolor:#FFFFFF][td]SWEPS D[/td][td]Sundae with extra pink sprinkles delivery[/td][td][/td][td]SPAM SPAM SPAM[/td][td][/td][td]SWEPS D for footy team[/td][td]Sundae with extra pink sprinkles delivery[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]PC[/td][td=bgcolor:#DDEBF7]Prank call[/td][td][/td][td=bgcolor:#DDEBF7]HB D [/td][td][/td][td=bgcolor:#E2EFDA]TM call centre (ignored!!)[/td][td=bgcolor:#E2EFDA]Telemarketer[/td][/tr]

[tr=bgcolor:#FFFFFF][td]TM[/td][td]Telemarketer[/td][td][/td][td]1$$$%32523123123[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]BCIOP[/td][td=bgcolor:#DDEBF7]Boss checking in on performance[/td][td][/td][td=bgcolor:#DDEBF7]PC 1 blocked the number...[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]BCIOP at closing time[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#DDEBF7]blah blah blah [/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]PACP[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#DDEBF7]&&&&&&&&[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]PAC_D[/td][td][/td][td][/td][td][/td][/tr]
[/table]

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

 

[sandy666]

green table should be like this:

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]User text[/td][td=bgcolor:#70AD47]Call Type[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]BCIOP at closing time[/td][td=bgcolor:#E2EFDA]Boss checking in on performance[/td][/tr]

[tr=bgcolor:#FFFFFF][td]HB D [/td][td]Hamburger delivery[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]HB P for harry[/td][td=bgcolor:#E2EFDA]Hamburger pickup[/td][/tr]

[tr=bgcolor:#FFFFFF][td]PACP[/td][td]Pie and chips pickup[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]PC 1 blocked the number...[/td][td=bgcolor:#E2EFDA]Prank call[/td][/tr]

[tr=bgcolor:#FFFFFF][td]SWEPS D for ballet team[/td][td]Sundae with extra pink sprinkles delivery[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]SWEPS D for footy team[/td][td=bgcolor:#E2EFDA]Sundae with extra pink sprinkles delivery[/td][/tr]

[tr=bgcolor:#FFFFFF][td]TM call centre (ignored!!)[/td][td]Telemarketer[/td][/tr]
[/table]

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