Transform Data („Cost“) in Google Data Studio from „Text“ to „Number“ does not work

Question

Situation:

I am about to create a Report in Google Data Studio based on Data copied from someone else into a Google Sheet.

This data includes the metric „cost“ („Gesamtkosten“). GDS identifies this as as text/string.

I want to transform it in GDS into number / currency to use it as a metric.

So far - so simple.

Already tried:

1.) Change Type of Data from „Text“ to „Currency“ / „Number“

2.) Changed Type of Data with CAST

CAST (Rechnungsbetrag Netto (Text) AS NUMBER)

3.) Replaced „€“ and „.“ and deleted Blancs with REPLACE and TRIM to minimize data on digits.

TRIM( REPLACE (REPLACE( Gesamtbetrag, ".",""), "€",""))

Data

Here is a Copy of the Google Sheet with the related Colume:

https://docs.google.com/spreadsheets/d/1b3L6p-tdiZRicQIlOHHr3iSLQTCbY7N1Nb1tcBCZv0I/edit

Thanx for any help!

Answer 1

Try this in cell D2:

=arrayformula( iferror( value( regexreplace(trim(C2:C); "[^-\d,]"; "") / sign(len(C2:C)) ) ) )

Format the result column as Format > Number > Currency.