Reformatting data
- 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
Hi All,
In my data I have a field that contains values such as:
31/12/2023 0.55|30/06/2024 0.45
25/07/2040 0.2345|18/04/2045 0.456|01/01/2047 0.3095
I need to change these into:
55 31/12/2023|45 30/06/2024
23.45 25/07/2040|45.6 18/04/2045|30.95 01/01/2047
Thus the number after the needs to be multiplied by 100 and put in from of the date.
Above is just an example. In the actual data, there could be for example 20 or 100 date/value pairs that are separated by a pipelline.
I assume regex can do this easily? What regex expression to use?
Thanks.
Solved! Go to Solution.
- Labels:
- Developer Tools
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @RVDL, for a problem like this, I prefer to clean it up over multiple steps. I have attached a workflow that splits all the unique strings, cleans them up using some regex and formulas, and then puts everything back together in the format you are looking for. Let me know what you think.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@RVDL here is one solution for you. Used a combination of Regex and Formula to solve it. I am sure there is a more elegant solution with Regex Replace, but this should help you out in the interim.
Copy of Solution
Regex Structure
Formula Restructure to Create Solution
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@phottovy thank you. This worked fine.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@rzdodson Thanks for your suggestion.
