Reformatting data

[RVDL]

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. 

[phottovy · Accepted solution]

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.

[rzdodson]

@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

[RVDL]

@phottovy thank you. This worked fine.

[RVDL]

@rzdodson Thanks for your suggestion.