SUBSTITUTE/IF Formula for specified and unspecified text in cells

[jakemoggy77]

I'm looking for a formula that can recognise specified text and then any text that follows that is not in the specified parameters is replaced with the specified text.

So for example the recognised text will be

Black
Blue
Brown

If something not specified follows such as 'spot' it will be replaced with Mix.

So

'Black' the formula would return 'Black'

'Black Spot' the formula would return 'Black Mix'

I've attached a file in which recognises the specified text and will return that but lacks the function of replacing the unspecified text.


I hope that explains it clearly, much appreciate the help.

Thanks in advance.

J

[WideBoyDixon]

Aasuming 2206 is O365 so try this in C3:

Please Login or Register  to view this content.

WBD

[HansDouwe]

Another option:

Please Login or Register  to view this content.

[jakemoggy77]

Thank you all, just wondering is there a way to have these formula's ignore anything after the second word?

So it would only ever be 'colour' 'mix' so you wouldn't have black mix mix etc?

[HansDouwe]

Please try: (changes in red)

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

[WideBoyDixon]

I also worked on this a little more:

Please Login or Register  to view this content.

WBD

[jakemoggy77]

These work really well. I may need to alter more depending on what combinations occur. Thanks all for the help.