Convert Text-Based RGBA Values into Colour

Have you ever had RGBA values stored in text format that need converting to actual, usable RGBA colour values?

I had this use case recently. It took a bit of Power Fx skullduggery so thought I’d share the method!

Initial RGBA data

In my scenario, the client had a table with RGBA values stored in a data source. The values were stored in Single Line of Text columns. Here’s an example of what I was working with – note the inconsistent spacing between each value:

Solution

The solution was to combine a mixture of the With, Substitute, Split, First and Index functions.

The Substitute function acts to remove the brackets and any spaces, Split then creates an object of references where a new line is created for every comma in the string. We use this in conjunction with Lower to ensure the formula will always work regardless of whether the source data has RGBA in upper or lower case. We can then use First and Index to determine after what number comma we need the value from.

Did all that make sense? Maybe not. It made sense when I worked through it! If you don’t care about the method to the madness and just want the Power Fx, please see below. The example is using a gallery hence the ThisItem.RGBATextValue reference, so update to your text string/variable/collection output as required:

				
					With(
    {
        tvRGBAText: Split(
            Substitute(
                Substitute(
                    ThisItem.RGBATextValue,
                    "RGBA(",""),")",""),",")
    },
    RGBA(
        Value(First(tvRGBAText).Value),
        Value(
            Index(
                tvRGBAText,
                CountRows(tvRGBAText) - 1
            ).Value
        ),
        Value(
            Index(
                tvRGBAText,
                CountRows(tvRGBAText) - 2
            ).Value
        ),
        Value(
            Index(
                tvRGBAText,
                CountRows(tvRGBAText) - 3
            ).Value
        )
    )
)
				
			

Here’s my example in all its working glory:

What do you think?

Your email address will not be published. Required fields are marked *

2 Comments
  • Anonymous
    April 25, 2025

    With(
    {
    parts: Split(Substitute(Substitute(ThisItem.FilterColor, “RGBA(“, “”), “)”, “”), “,”)
    },
    RGBA(
    Value(First(parts).Value),
    Value(First(LastN(parts, CountRows(parts) – 1)).Value),
    Value(First(LastN(parts, CountRows(parts) – 2)).Value),
    Value(First(LastN(parts, CountRows(parts) – 3)).Value)
    )
    )

    • Craig White
      April 27, 2025

      Hey there, great shout! This is a better approach than the one I originally posted. You can streamline this even more by replacing First/LastN with the Index function – see the updated code snippet above!