Pivot Table > Metric > Add Field > IF(CONTAINS_TEXT(field_name,"A"),5,field_name) doesn't work

[Chris-Southbank]

Hi All, I am wondering whether Google need to fix a bug. I have a field, field_name, that contains numbers and the letter "A" in some spaces.

I tried using the custom field IF(CONTAINS_TEXT(field_name,"A"),5,field_name) as a metric in a pivot table, and it's accepted.  When field_name evaluates to a number, the number is displayed in the pivot table. When it evaluates to A, I just get a blank cell, instead of 5.

If I replace CONTAINS_TEXT(...) with true, I get 5.

Can anyone tell me please if there is a way to make this formula do what I want it to do?  Thank you.

[sam8]

Sounds like the field is a text field? Maybe try wrapping the 5 in quotes so that it matches the field data type?

IF(CONTAINS_TEXT(field_name,"A"),"5",field_name)

[Chris-Southbank]

Hi, many thanks for your reply. I have moved on to something else for now, but will come back to this and let you know how it went.