REPLACING DASH CHARACTERS

Question

Hi I have a sample data here. I want to replace the "-" characters into 0 as this pertains to numeric columns but it was string when being imported. However, there is also a "-" at the start of the digits of some cells. I just want to replace the cells that have "-"  characters entirely.

CODEBALANCE1BALANCE2BALANCE3BALANCE439374937-7700521.9812000054656898-789-9000054656898--890652.468000054543649-2890.6760000-900054645648-600--6800054646477-9008090--

Expected Output:

CODEBALANCE1BALANCE2BALANCE3BALANCE43937493707700521.98120000546568980789090000546568980-890652.468000054543649-2890.6760000090005464564806000-6800054646477-900809000

Can you help me with this one?

DataNath · Accepted Answer

Hey @dunkindonut7777, you can use the Multi-Field Formula to handle several fields at once. If you're only looking to replace instances where the whole value is '-' then I'd go with this expression:

IF ToString([_CurrentField_]) = '-' THEN 0 ELSE [_CurrentField_] ENDIF

The benefit of this tool is you can also change the output's data type without creating a new field, so can cast your incoming strings to numeric values when doing the replacement (see the 'Change Output Type to' configuration: