Formatting within a Concatenate

Matt_H · 08-16-2019 02:20 PM

CONCATENATE("PN: “,TEXT([PN]),” ","Q: “,[Q],” “,“GRC:”,[GRC],” ","LOC: “,[LOCATION],” ","B: “,LEFT([BUYER],6),” ","P: “,LEFT([PLANNER],6),” ","T: “,LEFT([TECH],6),” ","DR: “,[DR],” ","HN: ",[HN])

I’m trying to get the TEXT in the columns such as [PN] to highlight within a virtual column. The regular text "PN: " can remain unchanged.There is no Format Rule that I can think of that works inside of a CONCATENATE expression. The TEXT() function above does not work. [PN] highlights on every other view except where it has been incorporated into a CONCATENATE.

Steve

You cannot format selected text within a concatenated textual value.

elboga81

I have the same problem, i created a virtual column that concatenates 5 columns. In Each column i have different values that i would like to highlight separately. Is there no way to get around this? "w" colored Green, "L" Coloured Red

Thank you

Suvrutt_Gurjar

If you are willing to use and experiment with SVGs , please take a look at various SVG posts in the Tips and Tricks section.

Search - Google Cloud Community

The example below shows 5 columns concatenated, each with a different color using SVG code in a image type VC in a detail view.

The code is something like below

CONCATENATE("data&colon;image/svg+xml;utf8,<svg xmlns=""http://www.w3.org/2000/svg"" viewBox=""0 0 80 50"">
<rect width=""700px"" height=""40px"" fill=""Black""/>

<text font-family=""Verdana"" font-size=""5px"" text-anchor=""left"" fill=""Red"" x=""5%"" y=""15%"">",CONCATENATE([Name]," ,")," </text>
<text font-family=""Verdana"" font-size=""5px"" text-anchor=""left"" fill=""Green"" x=""5%"" y=""35%"">",CONCATENATE([Street]," ,")," </text>
<text font-family=""Verdana"" font-size=""5px"" text-anchor=""left"" fill=""Yellow"" x=""5%"" y=""55%"">",CONCATENATE([City]," ,")," </text>
<text font-family=""Verdana"" font-size=""5px"" text-anchor=""left"" fill=""Orange"" x=""50%"" y=""55%"">",CONCATENATE("WA"," ,")," </text>
<text font-family=""Verdana"" font-size=""5px"" text-anchor=""left"" fill=""White"" x=""70%"" y=""55%"">",[Zip]," </text>

</svg>")

elboga81

Basically I want to concatenate 15 columns. 1 to 15. In each column i have 3 values "W" colored Green, "L" Coloured in Red and "D" Coloured in Orange, my virtual column i named "FORM" Problem is that i can do them separately but it takes too much space but once i concatenate i can colour each value separately, wish you can help

Suvrutt_Gurjar

I think you will need to share at least some example of your concatenation of what exactly you are looking for, meaning do you want to highlight the text in different colors , then you may need to go the SVG way.

If you can place some icons near the text of each column to indicate status, you could use emojis.

circle

There are several tips and tricks on emojis. Please evaluate if these suit you.

Search - Google Cloud Community

Finally, the community could suggest an approach. You will need to evaluate and create an expression etc. on your own that suits your needs.

elboga81

I havent concatenated anything as the feature is not possible. Clolumns go from 1 to 15

SO it would be like:

concatenate([1];[2];[3]....) so on and so forth till [15]

There is a column with a list of Tennis players

So depending on if a player wins, Draws or Loses there will be a different value in each column.

So Column 1 could have 3 different Values W,D,L depending on each players result.

If i have columns seprated appsheet creates an uneccesary huge column for each value, if i concatenate it it looks great,but, I need to colour each value eithe Green, yellow or Red. WOuld be awesome if i can actually create a Green, Yellow or Red square and (inside) the value of "W","D","L" Hope that you can help me out

Thank you so much

Suvrutt_Gurjar

Okay, you could concatenate using emojis for an easier implementation or you could use SVG approach for a more elaborate and neater implementation.

An example using emojis is shown below

elboga81

I dont really know how to use SVG. My example would be

Not concatenated will look like this

1 2 3
w w L
d w L
w L W
L L W

Works fine but takes too much unecessary space between columns.

If i concatenate it would look like this:

1 2 3
w w L
d w L
w L W
L L W

Problem here is that when you concatenate it doesnt allow you to create a format rule for separate values. Would be nice if they added that feature

Thanks for your help 😀

Suvrutt_Gurjar

As it was mentioned at the beginning of the post by @Steve , there cannot be different format rule within the text of a single column.

That is why I suggested two possible workaround options of SVGs or using emojis. You can include emoji just as a text based on the status of a column.

For example

IFS( [Column A] = "W", CONCATENATE( " Column A: ", "🟢" , " -W ", ),

[Column A]= "D" , CONCATENATE( " Column A: ", "🟡" , " -D ", ),

[Column A] = "L" , CONCATENATE( " Column A: ", "🔴" , " -L ", )

)

Please expand the concept of emojis if you like based on your actual requirement of logic.

dbaum

@Suvrutt_Gurjar wrote:

IFS( [Column A] = "W", CONCATENATE( " Column A: ", "🟢" , " -W ", ),

[Column A]= "D" , CONCATENATE( " Column A: ", "🟡" , " -D ", ),

[Column A] = "L" , CONCATENATE( " Column A: ", "🔴" , " -L ", )

)

FWIW: No need for a separate IFS function column by column. Instead, concatenate first and then substitute. Here's an example technique: Concatenate values with a delimiter: How to simula... - Google Cloud Community .

@elboga81 wrote:

WOuld be awesome if i can actually create a Green, Yellow or Red square and (inside) the value of "W","D","L"

Definitely possible with SVG. If you're interested in tackling this approach, here's an image to get you started. You can extend it to include more than 5 items and you would replace the colored square emoji characters and the W/D/L values with expressions based on your columns.

data&colon;image/svg+xml;utf8,<svg width="196" height="196" xmlns="http://www.w3.org/2000/svg" xmlns:svg="http://www.w3.org/2000/svg">
 <!-- Created with SVG-edit - https://github.com/SVG-Edit/svgedit-->
 <title>RESULTS</title>
 <g class="layer">
  <title>Layer 1</title>
  <g id="svg_RESULT" transform="matrix(1 0 0 1 0 0)">
   <text fill="%23038f48" font-family="Helvetica Neue" font-size="24" id="svg_58" stroke="%23038f48" stroke-width="0" text-anchor="middle" x="480.5" xml:space="preserve" y="69.17"/>
   <g id="svg_RESULT_1">
    <text fill="%23038f48" font-family="Helvetica Neue" font-size="30" id="svg_60" stroke="%23038f48" stroke-width="0" text-anchor="middle" x="19.59" xml:space="preserve" y="70.25">🟩</text>
    <text fill="%23ffffff" font-family="Helvetica Neue" font-size="24" font-weight="bold" id="svg_61" stroke="%23000000" stroke-opacity="0" stroke-width="0" text-anchor="middle" x="19.79" xml:space="preserve" y="68.3">W</text>
   </g>
   <g id="svg_RESULT_2">
    <text fill="%23038f48" font-family="Helvetica Neue" font-size="30" id="svg_63" stroke="%23038f48" stroke-width="0" text-anchor="middle" x="58.54" xml:space="preserve" y="70.25">🟥</text>
    <text fill="%23ffffff" font-family="Helvetica Neue" font-size="24" font-weight="bold" id="svg_64" stroke="%23000000" stroke-opacity="0" stroke-width="0" text-anchor="middle" x="58.73" xml:space="preserve" y="68.3">L</text>
   </g>
   <g id="svg_RESULT_3">
    <text fill="%23038f48" font-family="Helvetica Neue" font-size="30" id="svg_66" stroke="%23038f48" stroke-width="0" text-anchor="middle" x="97.5" xml:space="preserve" y="70.25">🟨</text>
    <text fill="%23ffffff" font-family="Helvetica Neue" font-size="24" font-weight="bold" id="svg_67" stroke="%23000000" stroke-opacity="0" stroke-width="0" text-anchor="middle" x="97.69" xml:space="preserve" y="68.3">D</text>
   </g>
   <g id="svg_RESULT_4">
    <text fill="%23038f48" font-family="Helvetica Neue" font-size="30" id="svg_69" stroke="%23038f48" stroke-width="0" text-anchor="middle" x="136.45" xml:space="preserve" y="70.25">🟥</text>
    <text fill="%23ffffff" font-family="Helvetica Neue" font-size="24" font-weight="bold" id="svg_70" stroke="%23000000" stroke-opacity="0" stroke-width="0" text-anchor="middle" x="136.64" xml:space="preserve" y="68.3">L</text>
   </g>
   <g id="svg_RESULT_5">
    <text fill="%23038f48" font-family="Helvetica Neue" font-size="30" id="svg_72" stroke="%23038f48" stroke-width="0" text-anchor="middle" x="175.4" xml:space="preserve" y="70.25">🟩</text>
    <text fill="%23ffffff" font-family="Helvetica Neue" font-size="24" font-weight="bold" id="svg_73" stroke="%23000000" stroke-opacity="0" stroke-width="0" text-anchor="middle" x="175.6" xml:space="preserve" y="68.3">W</text>
   </g>
  </g>
 </g>
</svg>

elboga81

Would be nice if they added that feature. I have columns names from 1 to 15.

CONCATENATE([1];[2];[3];....[15]

Each column has 3 values W D L

When concatenated, it doesnt allow me to colour each letter separetly

🙏

dbaum

Here's another alternative technique you can explore: Rich Text Formatting - in Preview Program - Google Cloud Community . Text color isn't supported, but maybe something that is supported would suffice. Otherwise, you still have the other alternative techniques detailed in this thread.

@elboga81 wrote:

Would be nice if they added that feature.

Feature Ideas - Google Cloud Community

elboga81

Its frustrating that you cant have 4 pictures on one row on a Deck view