Change a macro into a custom function

[EJ5916]

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?  

[binuacs]

@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

[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?  

[binuacs · Accepted solution]

@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.  

[binuacs]

@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 · Accepted solution]

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