If condition with text

[Moranh]

Hi,

I have this column 

And I would like to create a condition which will build the below

1) Can I distinguish text with the ; mark?

2) Do I need to create the color columns, or can I have a condition to create them automatically? 

3) How can I overcome the issue of the number and order of value may change and can be even empty?

4) How can I make sure that the data in these new columns will not be removed, when I'm doing an update from the DB

Thanks in advance! :)

[#2 · Accepted solution]

@Moranh 

Here is a small solution ...

If it corresponds to your ideas as a solution, please mark it as the correct answer, so that other informed can be informed

... best with a like. If you did not like the offered solution, please give a short feedback.


Nikolino
I know I don't know anything (Socrates)

[#3]

@Moranh 

Here again with conditional formatting :)

[#4]

@NikolinoDE 

Thank you so much! Is there any way to create the headline of the colors automatically? Or I must create them manually? 

[#5]

I was glad that I could help you

Excel
Select area where it should appear
Start
Conditional formatting
New rule
Format only cells that contain
first selection: certain text
second selection: starting with
third selection: enter the desired letter (example: R = Red, as entered in the cell forml)
Format ... according to your wishes
OK done


Nikolino
I know I don't know anything (Socrates)

[#6]

@Moranh , Here's a Power Query solution. You can add more values to the blue table on the left and click on Data->Refresh All to update the formatted table. It will automatically pick the colors from the blue table without need for manual entry.