Conditional color formatting of individual characters in a cell

%3CLINGO-SUB%20id%3D%22lingo-sub-3496086%22%20slang%3D%22en-US%22%3EConditional%20color%20formatting%20of%20individual%20characters%20in%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3496086%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20day%20-%20thanks%20in%20advance%20for%20your%20help!%3C%2FP%3E%3CP%3EI%20use%20Excel%20to%20manage%20G-code%20for%20machining%20processes%20at%20my%20shop.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20would%20be%20awesome%20if%20I%20can%20condition%20cells%20containing%20G-Code%20in%20this%20way%3A%3C%2FP%3E%3CP%3EEvery%20character%20in%20a%20cell%20that%20is%20a%20number%20%5B12345%5D%20is%20black.%3C%2FP%3E%3CP%3EEvery%20character%20in%20a%20cell%20that%20is%20a%20letter%20%5B%3CFONT%20color%3D%22%23DF0000%22%3EAbcdef%3C%2FFONT%3E%5D%20is%20red.%3C%2FP%3E%3CP%3EEvery%20set%20of%20parentheses%20%3CFONT%20color%3D%22%23008000%22%3E(and%20their%20contents)%3C%2FFONT%3E%20is%20green.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20cell%20may%20contain%20content%20like%20this%20when%20formatted%3A%26nbsp%3B%20%5B%3CFONT%20color%3D%22%23DF0000%22%3EG%3C%2FFONT%3E00%20%3CFONT%20color%3D%22%23DF0000%22%3EX%3C%2FFONT%3E3.0%20%3CFONT%20color%3D%22%23DF0000%22%3EY%3C%2FFONT%3E5.25%20%3CFONT%20color%3D%22%23008000%22%3E(Rapid%20to%20position)%3C%2FFONT%3E%5D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20looked%20over%20conditional%20formatting%20but%20don't%20see%20the%20ability%20to%20influence%20colors%20at%20the%20character%20level.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20this%20possible%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3CP%3EJohn%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3496086%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3496130%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20color%20formatting%20of%20individual%20characters%20in%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3496130%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1421157%22%20target%3D%22_blank%22%3E%40AllenCab%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAwesome%2C%20maybe.%20Then%20again%2C%20maybe%20not.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20don't%20say%20%3CU%3E%3CSTRONG%3Ewhy%3C%2FSTRONG%3E%3C%2FU%3E%20you'd%20like%20to%20do%20this.%20For%20the%20record%2C%20I%20don't%20really%20think%20it's%20possible%20(not%20using%20conditional%20formatting%20at%20any%20rate)%3B%20and%20I'd%20caution%20against%20it%20anyway%2C%20unless%20there's%20some%20compelling%20reason.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhy%20caution%3F%20As%20I%20said%20already%2C%20unless%20there's%20some%20compelling%20reason--some%20essential%20functionality%20that%20is%20going%20to%20be%20missing%20from%20the%20machining%20process%2C%20some%20vulnerability%20to%20major%20disaster%20that%20can%20only%20be%20averted%20by%20that%20color%20coding--then%20I'd%20see%20it%20as%20fancy%20clutter.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELet's%20think%20about%20an%20alternative.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20you've%20got%20in%20that%20cell%20is%20fairly%20clearly%20multi-level%20meaning%3B%20it%20is%20not%20only%20a%20G-Code-itself%20with%20multiple%20meanings--%2C%20but%20a%20parenthetical%20that%20also%20has%20its%20own%20meaning.%20A%20word%20to%20the%20wise%3A%20in%20data%20processing%2C%20it's%20generally%20a%20mistake%20to%20make%20one%20field%20(aka%20one%20cell)%20perform%20multiple%20functions.%20It%20can%20lead%20to%20confusion%3B%20it%20can%20lead%20to%20disaster.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEach%20of%20those%20letters--%20%3CFONT%20color%3D%22%23DF0000%22%3EG%2C%20X%2C%20Y%3C%2FFONT%3E%20in%20your%20example--has%20certain%20meaning.%20Presumably%20there%20are%20other%20letters%2C%20each%20with%20its%20own%20meaning%2C%20which%20could%20be%20in%20those%20three%20spaces.%20There%20are%20presumably%20other%20potential%20parenthetical%20phrases.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20suggestion%20(which%2C%20given%20that%20I'm%20speculating%20from%20the%20get-go%2C%20can%20be%20tossed%20aside)%20would%20be%20to%20take%20those%20multi%3Dleveled%20meanings%20embedded%20in%20those%20letters%2C%20and%20give%20them%20cells%20of%20their%20own%20where%20you%20%3CSTRONG%3E%3CEM%3E%3CU%3Ecould%3C%2FU%3E%3C%2FEM%3E%3C%2FSTRONG%3E%20employ%20conditional%20formatting%20to%20highlight%20whatever%20it%20is%20that%20requires%20urgent%20attention%2C%20urgent%20and%20unambiguous.%20I%20would%20think%20that%20could%20be%20accomplished%20with%20four%20more%20cells%20per%20row%2C%20one%20for%20each%20of%20those%20letters%20and%20one%20slightly%20larger%20for%20the%20parenthetical%20phrase.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETake%20%3CFONT%20color%3D%22%23DF0000%22%3Eit%3C%2FFONT%3E%20for%20what%20it's%20%3CFONT%20color%3D%22%23007A4B%22%3Eworth%3C%2FFONT%3E.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Good day - thanks in advance for your help!

I use Excel to manage G-code for machining processes at my shop.

 

It would be awesome if I can condition cells containing G-Code in this way:

Every character in a cell that is a number [12345] is black.

Every character in a cell that is a letter [Abcdef] is red.

Every set of parentheses (and their contents) is green.

 

A cell may contain content like this when formatted:  [G00 X3.0 Y5.25 (Rapid to position)]

 

I've looked over conditional formatting but don't see the ability to influence colors at the character level.

 

Is this possible?

 

Thank you!

John

1 Reply

@AllenCab 

 

Awesome, maybe. Then again, maybe not.

 

You don't say why you'd like to do this. For the record, I don't really think it's possible (not using conditional formatting at any rate); and I'd caution against it anyway, unless there's some compelling reason.

 

Why caution? As I said already, unless there's some compelling reason--some essential functionality that is going to be missing from the machining process, some vulnerability to major disaster that can only be averted by that color coding--then I'd see it as fancy clutter.

 

Let's think about an alternative.

 

What you've got in that cell is fairly clearly multi-level meaning; it is not only a G-Code-itself with multiple meanings--, but a parenthetical that also has its own meaning. A word to the wise: in data processing, it's generally a mistake to make one field (aka one cell) perform multiple functions. It can lead to confusion; it can lead to disaster.

 

Each of those letters-- G, X, Y in your example--has certain meaning. Presumably there are other letters, each with its own meaning, which could be in those three spaces. There are presumably other potential parenthetical phrases.

 

My suggestion (which, given that I'm speculating from the get-go, can be tossed aside) would be to take those multi=leveled meanings embedded in those letters, and give them cells of their own where you could employ conditional formatting to highlight whatever it is that requires urgent attention, urgent and unambiguous. I would think that could be accomplished with four more cells per row, one for each of those letters and one slightly larger for the parenthetical phrase.

 

Take it for what it's worth