Ok @Jenny Franklin and @Chris Davidson, I'm going to follow your advice from the Atlanta Local User Group this past week and actually post a question on community rather than silently trying to figure it out on my own.  Here goes...

We receive a dataset each month that links mainframe consumption to an application. The mapping file includes the internal Application ID used by our company. The Application ID is a string of numeric characters which we must treat as a label due to constraints upon other datasets in our model.

Here's the issue:

The mainframe dataset comes to us with the Application ID formatted as a number, including commas as group separators. In the screenshot below you can see a sampling of Application IDs: 37, 137 and 1,037.

That 1,037 is a problem for us.  I need that string of characters to simply be 1037 without a comma (and retain the Label type)

I'm sure there is a simple formula that will do the trick.  So I'm gonna lean on the Apptio community for this one.

I know somebody out there has already encountered and solved this issue.

Thank you in advance!

I wonder if you could use this ReplaceRegex function.ReplaceRegex function 

Thank you @Jason Tucker for the suggestion.  I discovered that I could use the Substitute formula to search for the comma and replace it with "nothing."

=Substitute({app_id},",","")

Works like a charm!

regexreplace({app_id},",","")

@Matthew Poletiek, you and @Jason Tucker have both referenced the ReplaceRegEx function. Do you know if there is a benefit in using his function versus Substitute?

the substitute function sounds much simpler. I would go with that first and see if it works.

Eric –

I have never used ReplaceRegEx, but it appears to have more extensive capabilities than Substitute which is simply doing character substitution. If you are simply removing comas I would use Substitute.

Zach

Zach Cartozian

415.710.8088

zxcartozian@aetna.com

Eric –

Try Substitute function -  for example =Substitute(Billing ID,",","") should replace coma with nothing.

Zach

Zach Cartozian

415.710.8088

zxcartozian@aetna.com

Good question.

There is a chance regexreplace is more compute expensive than substitute, but that depends on whats happening behind the scenes. I'd defer to an Apptio developer. 

Personally I default to regex functions for string manipulation because its a lot more powerful than simple substitute or split. I have a lot more options for matching, but it might be more compute intensive for this simple application. 

Told ya Community was an awesome resource!!   #dropsmic #walksaway lol..

@Jenny Franklin is there a badge called "You Were Right!" that I could award you?!

Thanks for the suggestion.

LOL   Glad you witnessed firsthand the power of Community!  It's so Google for Apptio and TBM 

i learnt about the function regexreplace() today. Thanks!

Me too!