Change a macro into a custom function

Is there a way to save a macro as a custom function to use in the formula tool?

I'm trying to change some mainframe fields that I parsed out from EBCDIC format (00999870D) into a readable decimal. (99987.04)

But I have over 100 fields across 4 different files so I'm not sure how best to send these all thru the macro.

Is there a way to save the macro as a function so I could convert the field in the formula tool? I would do this in Excel VBA and call it like =EBCDICtoDec([Field])

Should I be using something else to do this?

EBCDICtoDec.yxmc

Best Practices

Batch Macro

Accepted answers

binu_acs

@EJ5916 if you have more fields then transpose the data and run through the batch macro, cross tab the result

EJ5916

I ended up using the Multi-Field Formula tool with this formula:

ToNumber(if RIGHT([_CurrentField_],1) ='}' then (-1) * ToNumber(Substring([_CurrentField_],0,Length([_CurrentField_])-1)+"0")

elseif RIGHT([_CurrentField_],1) ='J' then (-1) * ToNumber(Substring([_CurrentField_],0,Length([_CurrentField_])-1)+"1")
elseif RIGHT([_CurrentField_],1) ='K' then (-1) * ToNumber(Substring([_CurrentField_],0,Length([_CurrentField_])-1)+"2")
elseif RIGHT([_CurrentField_],1) ='L' then (-1) * ToNumber(Substring([_CurrentField_],0,Length([_CurrentField_])-1)+"3")
elseif RIGHT([_CurrentField_],1) ='M' then (-1) * ToNumber(Substring([_CurrentField_],0,Length([_CurrentField_])-1)+"4")
elseif RIGHT([_CurrentField_],1) ='N' then (-1) * ToNumber(Substring([_CurrentField_],0,Length([_CurrentField_])-1)+"5")
elseif RIGHT([_CurrentField_],1) ='O' then (-1) * ToNumber(Substring([_CurrentField_],0,Length([_CurrentField_])-1)+"6")
elseif RIGHT([_CurrentField_],1) ='P' then (-1) * ToNumber(Substring([_CurrentField_],0,Length([_CurrentField_])-1)+"7")
elseif RIGHT([_CurrentField_],1) ='Q' then (-1) * ToNumber(Substring([_CurrentField_],0,Length([_CurrentField_])-1)+"8")
elseif RIGHT([_CurrentField_],1) ='R' then (-1) * ToNumber(Substring([_CurrentField_],0,Length([_CurrentField_])-1)+"9")

elseif RIGHT([_CurrentField_],1) ='{' then ToNumber(Substring([_CurrentField_],0,Length([_CurrentField_])-1)+"0")
elseif RIGHT([_CurrentField_],1) ='A' then ToNumber(Substring([_CurrentField_],0,Length([_CurrentField_])-1)+"1")
elseif RIGHT([_CurrentField_],1) ='B' then ToNumber(Substring([_CurrentField_],0,Length([_CurrentField_])-1)+"2")
elseif RIGHT([_CurrentField_],1) ='C' then ToNumber(Substring([_CurrentField_],0,Length([_CurrentField_])-1)+"3")
elseif RIGHT([_CurrentField_],1) ='D' then ToNumber(Substring([_CurrentField_],0,Length([_CurrentField_])-1)+"4")
elseif RIGHT([_CurrentField_],1) ='E' then ToNumber(Substring([_CurrentField_],0,Length([_CurrentField_])-1)+"5")
elseif RIGHT([_CurrentField_],1) ='F' then ToNumber(Substring([_CurrentField_],0,Length([_CurrentField_])-1)+"6")
elseif RIGHT([_CurrentField_],1) ='G' then ToNumber(Substring([_CurrentField_],0,Length([_CurrentField_])-1)+"7")
elseif RIGHT([_CurrentField_],1) ='H' then ToNumber(Substring([_CurrentField_],0,Length([_CurrentField_])-1)+"8")
elseif RIGHT([_CurrentField_],1) ='I' then ToNumber(Substring([_CurrentField_],0,Length([_CurrentField_])-1)+"9")

else [_CurrentField_]
endif) * .01

All comments

binu_acs

@EJ5916 you cannot call the macro inside the formula tool but if you convert the macro into a batch macro you can use this in your dataset. Sample attached

EBCIDIC_Converter_210224.yxzp

EJ5916

I have 100 fields in this format, are you saying I have add a macro icon for each field? Isn't there a better way?

binu_acs

@EJ5916 if you have more fields then transpose the data and run through the batch macro, cross tab the result

EJ5916

what would that looks like? I have 171,000 rows and 100 columns.

binu_acs

@EJ5916 I can only suggest options, another option I'm thinking create a formula that converts EBCDIC format to decimal format, use the save expression option in the formula tool to save the formula then re-use, giving you a sample formula

EJ5916

I ended up using the Multi-Field Formula tool with this formula:

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703