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.

Developer Tools

Accepted answers

phottovy

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.

Reformatting Data.yxmd

All comments

phottovy

Reformatting Data.yxmd

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

3 - Solution.png

Regex Structure

1 -Regex.png

Formula Restructure to Create Solution

2 - Formula Restructure.png

4 - Solution.yxzp

RVDL

@phottovy thank you. This worked fine.

RVDL

@rzdodson Thanks for your suggestion.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703