SOLVED

Change color attribute on an entry in CONCAT

%3CLINGO-SUB%20id%3D%22lingo-sub-1286897%22%20slang%3D%22en-US%22%3EChange%20color%20attribute%20on%20an%20entry%20in%20CONCAT%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1286897%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20be%20able%20to%20change%20the%20color%20of%20a%20single%20entry%20in%20a%20concatenated%20string%2C%20not%20the%20entire%20string%20I'm%20concatenating%20several%20text%20fields%20and%20a%20summed%20entry%20to%20create%20a%20message.%20If%20the%20summed%20entry%20is%20positive%20it%20will%20default%20to%20black%20but%20if%20it%20is%20negative%20it%20needs%20to%20be%20red%20and%20in%20parentheses.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eexample%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DCONCAT(%22This%20%22%2C%22is%20a%20positive%20field%20-%20%22%2CTEXT(SUM(1.50%2B2.00)%2C%22%24%23%23.00%22))%20should%20be%3A%3C%2FP%3E%3CP%3EThis%20is%20a%20positive%20field%20%243.50%3C%2FP%3E%3CP%3EWhile%3A%3C%2FP%3E%3CP%3E%3DCONCAT(%22This%20%22%2C%22is%20a%20negative%20field%20-%20%22%2CTEXT(SUM(1.50-2.00)%2C%22%24%23%23.00%22))%20should%20be%3A%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23FF0000%22%3E%3CFONT%20color%3D%22%23000000%22%3EThis%20is%20a%20negative%20field%3C%2FFONT%3E%26nbsp%3B(%243.50)%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23000000%22%3EHelp%2C%20please.%20I've%20tried%20all%20I%20can%20come%20up%20with%20and%20all%20suggestions%20revolve%20around%20changing%20the%20color%20of%20the%20entire%20cell%2C%20not%20just%20one%20entry%20in%20a%20formula.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETheOldPuterMan%20(AKA%20John)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1286897%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1287657%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20color%20attribute%20on%20an%20entry%20in%20CONCAT%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1287657%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F317593%22%20target%3D%22_blank%22%3E%40TheOldPuterMan%3C%2FA%3E%26nbsp%3BIt's%20not%20possible%20-%20rich%20formatting%20like%20this%20(formatting%20to%20only%20part%20of%20a%20cell)%20isn't%20possible%20with%20formulas.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1314681%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20color%20attribute%20on%20an%20entry%20in%20CONCAT%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1314681%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F544433%22%20target%3D%22_blank%22%3E%40Savia%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3BTanks%2C%20as%20I%20found%20the%20hard%20way%20lol.%20There%20are%20Add-Ins%20that%20will%20do%20it%20but%20for%20what%20I%20want%20it%20is%20totally%20unnecessary.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20reply%20though.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETheOldPuterMan%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Contributor

Hello,

 

I need to be able to change the color of a single entry in a concatenated string, not the entire string I'm concatenating several text fields and a summed entry to create a message. If the summed entry is positive it will default to black but if it is negative it needs to be red and in parentheses.

 

example:

 

=CONCAT("This ","is a positive field - ",TEXT(SUM(1.50+2.00),"$##.00")) should be:

This is a positive field $3.50

While:

=CONCAT("This ","is a negative field - ",TEXT(SUM(1.50-2.00),"$##.00")) should be:

This is a negative field ($3.50)

 

Help, please. I've tried all I can come up with and all suggestions revolve around changing the color of the entire cell, not just one entry in a formula.

 

TheOldPuterMan (AKA John)

2 Replies
Highlighted
Best Response confirmed by TheOldPuterMan (Contributor)
Solution

@TheOldPuterMan It's not possible - rich formatting like this (formatting to only part of a cell) isn't possible with formulas.

Highlighted

@Savia 

 Tanks, as I found the hard way lol. There are Add-Ins that will do it but for what I want it is totally unnecessary.

 

Thanks for the reply though.

 

TheOldPuterMan