Replace Last Instance of Character in String
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hello All,
I am working with a set of data that I need to concatenate into one field in the form of a sentence - I have been successfully able to concatenate this data using formulas, but I am running into an issue with one of our formatting requirements. The data elements must be separated out by commas for the most part, except for the last delimiter which must take the form of " and ". Please see below for examples of what I have and what I need.
Ex. Current - bank account number,credit card number,address
Ex. Needed - bank account number,credit card number and address.
I had the idea to attempt to use the below formula in the formula tool to achieve this (where "Summary" is my concatenated field, but I am receiving the below error when i try this. Any help would be greatly appreciated!
Formula : Left([Summary], Length([Summary]-FindString(ReverseString([Summary]),","))+" and "+Right([Summary], Length([Summary]-FindString([Summary], " and "))))
Error: "Formula: invalid type in subtraction operator"
Solved! Go to Solution.
- Labels:
- Workflow
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Here's a quick solve:
REGEX_Replace([Field1], "(.*),(.*)", '$1 and $2')
group 1 is everything before last comma
group 2 is everything after last comma
Cheers,
Mark
Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks Mark! Worked perfectly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
really cool and efficient!
