Modifying string text and numbers into same columns

[akumar2609]

Hi All,

I need support in the below query. I have 4 different columns (v-string data type). In this columns I have both numbers as well as text (in some of the places, we will have written as 'Blank' and on some of the places it will simply empty. Now I want if my data is in number then add % sign. For the text, for the -, for the Blank cells, I need the data as it is.

For example:-

CCM_A	CCA_A	CCM_B	CCA_B
10		5.6	5.2
-	Blank	-	5
-	Blank
5	-	12	0
100	16
5	8

Required Result

CCM_A	CCA_A	CCM_B	CCA_B
10%		5.6%	5.2%
-	Blank	-	5%
-	Blank
5%	-	12%	0%
100%	16%
5%	8%

[DataNath]

Hey @akumar2609, you can use a Multi-Field Formula for this in order to handle multiple fields at the same time. The expression will look something like so:

if regex_countmatches([_CurrentField_],'\d') > 0 then [_CurrentField_]+'%' else [_CurrentField_] endif

Not sure why but Alteryx has changed the '-' to 0 in some of my records but the workflow still shows the expression in action. Hope this helps!

[PhilipMannering]

You can also use this expression in the Multi-Field Formula Tool,

if IsInteger(replace(ToString([_CurrentField_]), '.', '')) 
then ToString([_CurrentField_]) + '%' 
else [_CurrentField_] endif