SOLVED

If condition with text

%3CLINGO-SUB%20id%3D%22lingo-sub-1555271%22%20slang%3D%22en-US%22%3EIf%20condition%20with%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1555271%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20have%20this%20column%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Moranh_0-1596099768501.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F208971iEA324E270E0363A8%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Moranh_0-1596099768501.png%22%20alt%3D%22Moranh_0-1596099768501.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3BAnd%20I%20would%20like%20to%20create%20a%20condition%20which%20will%20build%20the%20below%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Moranh_1-1596099822865.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F208972i54BB2733E2BB34FD%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Moranh_1-1596099822865.png%22%20alt%3D%22Moranh_1-1596099822865.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E1)%20Can%20I%20distinguish%20text%20with%20the%20%3B%20mark%3F%3C%2FP%3E%3CP%3E2)%20Do%20I%20need%20to%20create%20the%20color%20columns%2C%20or%20can%20I%20have%20a%20condition%20to%20create%20them%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance!%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1555271%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1555335%22%20slang%3D%22de-DE%22%3ESubject%3A%20If%20condition%20with%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1555335%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F744550%22%20target%3D%22_blank%22%3E%40Moranh%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22text-wrap%20tlid-copy-target%22%3E%3CDIV%20class%3D%22result-shield-container%20tlid-copy-target%22%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EHere%20is%20a%20small%20solution%20...%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%20%3CSPAN%20class%3D%22%22%3EIf%20it%20corresponds%20to%20your%20ideas%20as%20a%20solution%2C%20please%20mark%20it%20as%20the%20correct%20answer%2C%20so%20that%20other%20informed%20can%20be%20informed%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%20class%3D%22result-shield-container%20tlid-copy-target%22%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3E...%20best%20with%20a%20like.%3C%2FSPAN%3E%20%3CSPAN%3EIf%20you%20didn't%20like%20the%20offered%20solution%2C%20please%20give%20a%20short%20feedback.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%20%3CSPAN%3ENikolino%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1555348%22%20slang%3D%22de-DE%22%3ESubject%3A%20If%20condition%20with%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1555348%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F744550%22%20target%3D%22_blank%22%3E%40Moranh%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EHere%20again%20with%20conditional%20formatting%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1555354%22%20slang%3D%22en-US%22%3EBetreff%3A%20If%20condition%20with%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1555354%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-ERR%3AREF-NOT-FOUND-%40Nikolino%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20so%20much!%20Is%20there%20any%20way%20to%20create%20the%20headline%20of%20the%20colors%20automatically%3F%20Or%20I%20must%20create%20them%20manually%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1555374%22%20slang%3D%22de-DE%22%3ESubject%3A%20If%20condition%20with%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1555374%22%20slang%3D%22de-DE%22%3EI%20was%20glad%20that%20I%20could%20help%20you%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20Excel%20Select%20area%20where%20it%20should%20appear%20Start%20formatting%20Conditional%20New%20rule%20Format%20only%20%3CBR%20%2F%3E%20cells%20that%20contain%20first%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20selection%3A%20certain%20text%20second%20%3CBR%20%2F%3E%20selection%3A%20starting%20with%20%3CBR%20%2F%3E%20third%20selection%3A%20enter%20the%20desired%20letter%20(example%3A%20R%20%3D%20Red%2C%20as%20entered%20in%20the%20cell%20forml)%20%3CBR%20%2F%3E%20Format%20...%20According%20to%20your%20wishes%20%3CBR%20%2F%3E%20OK%20done%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%20Nikolino%20I%20know%20I%20%3CBR%20%2F%3E%20don't%20know%20anything%20(Socrates)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1557370%22%20slang%3D%22en-US%22%3EBetreff%3A%20If%20condition%20with%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1557370%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F744550%22%20target%3D%22_blank%22%3E%40Moranh%3C%2FA%3E%26nbsp%3B%2C%20Here's%20a%20Power%20Query%20solution.%20You%20can%20add%20more%20values%20to%20the%20blue%20table%20on%20the%20left%20and%20click%20on%20Data-%26gt%3BRefresh%20All%20to%20update%20the%20formatted%20table.%20It%20will%20automatically%20pick%20the%20colors%20from%20the%20blue%20table%20without%20need%20for%20manual%20entry.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi,

I have this column 

Moranh_0-1596099768501.png

 

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

 

Moranh_1-1596099822865.png

  

 

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!

5 Replies
Best Response confirmed by Moranh (Occasional Contributor)
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)

@Moranh 

 

Here again with conditional formatting

 

@Nikolino 

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

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)

@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.