Change Null to NA

Haokun

Hi

I have a set of data below, you will notice some of the columns are shown as 0 or null. So what I will need is for all the columns do not contain any "vs" in the header, I want to change the 0 or null to "N/A", for columns header contains "vs", which is to compare the data between 2 columns and if there is any data shown as "N/A" then the vs column will be shown as N/A as well.

Below is the current data

ENTITY ID	C-1M Fund	C-1M BM	MS-1M Fund	MS-1M BM	ES-1M Fund	ES-1M BM	C vs M-1M Fund	C vs M-1M BM	C vs ES-1M Fund	C vs ES-1M BM	M vs ES-1M Fund	M vs ES-1M BM
ASCIFD	-        1.11	-        0.62	2.23	2.23	-        1.11	-        0.62	-        3.34	-        2.85	0.00	-	3.34	2.85
ASGMAN	-        2.12	-        1.53	-        0.99	-        2.63	-	-	-        1.13	1.10	-        2.12	-        1.53	-        0.99	-        2.63
ASGMAN_D	-        2.12	-        1.53	-        1.98	-        2.63	-	-	-        0.13	1.10	-        2.12	-        1.53	-        1.98	-        2.63
ASPAIF	-        0.94	-        1.89	0.17	2.44	-        0.94	-        1.89	-        1.11	-        4.33	-        0.00	-	1.11	4.33
ASPAMF	-        3.62	-        3.45	-        3.03	-        2.66	-	-	-        0.59	-        0.78	-        3.62	-        3.45	-        3.03	-        2.66
ASPEEF	-        0.91	-        2.94	-        6.80	-        5.61	-        0.91	-        2.94	5.89	2.67	-        0.00	-	-        5.89	-        2.67
ASPEMF	-        1.65	-        1.21	3.77	2.09	-	-	-        5.42	-        3.30	-        1.65	-        1.21	3.77	2.09
ASPEMI	-        2.22	0.42	-        0.36	0.05	-	-	-        1.87	0.38	-        2.22	0.42	-        0.36	0.05

And the data output i want is 

ENTITY ID	C-1M Fund	C-1M BM	MS-1M Fund	MS-1M BM	ES-1M Fund	ES-1M BM	C vs M-1M Fund	C vs M-1M BM	C vs ES-1M Fund	C vs ES-1M BM	M vs ES-1M Fund	M vs ES-1M BM
ASCIFD	-        1.11	-        0.62	2.23	2.23	-        1.11	-        0.62	-        3.34	-        2.85	0.00	-	3.34	2.85
ASGMAN	-        2.12	-        1.53	-        0.99	-        2.63	N/A	N/A	-        1.13	1.10	N/A	N/A	N/A	N/A
ASGMAN_D	-        2.12	-        1.53	-        1.98	-        2.63	N/A	N/A	-        0.13	1.10	N/A	N/A	N/A	N/A
ASPAIF	-        0.94	-        1.89	0.17	2.44	-        0.94	-        1.89	-        1.11	-        4.33	-        0.00	-	1.11	4.33
ASPAMF	-        3.62	-        3.45	-        3.03	-        2.66	N/A	N/A	-        0.59	-        0.78	N/A	N/A	N/A	N/A
ASPEEF	-        0.91	-        2.94	-        6.80	-        5.61	-        0.91	-        2.94	5.89	2.67	-        0.00	-	-        5.89	-        2.67
ASPEMF	-        1.65	-        1.21	3.77	2.09	N/A	N/A	-        5.42	-        3.30	N/A	N/A	N/A	N/A
ASPEMI	-        2.22	0.42	-        0.36	0.05	N/A	N/A	-        1.87	0.38	N/A	N/A	N/A	N/A

Emil_Kos

Hi,

In a situation like this mutlifield formula is really useful. I have used below formula to sort your issue:

IIF([_CurrentField_]='-', 'N\A', [_CurrentField_])

When I copy pasted the null positions become '-'

In your exemple if nulls are really nulls you should use a formula like this:

IIF(isnull([_CurrentField_]), 'N\A', [_CurrentField_])

I have prepared a workflow for you: 

The output:

Just please remember if this will not work you need to replace the formula with this one: 

IIF(isnull([_CurrentField_]), 'N\A', [_CurrentField_])

If this was helpful please mark as a solution!

MultiField.yxmd