Formatting Data based on a masking value

[kayogunbo]

Dear Expert,

I need to format my SAP WBS data to have the required dots. based on a masking value. i have included the most common type of WBS that i am working with and the associated way to mask them.

In the attached spreadsheet, the data from SAP is in Column E, the masking type is in column G and the expected output is in column H.

Any help is appreciated.

Many thanks!

Kay O.

[Adrian_T · Accepted solution]

Hey @kayogunbo 

Here's one way of doing it. We can first determine your masking logic of POSID through calculating the length between each dot.

What's left is using the Substring Formula to filter out the corresponding values in the WBSE column before concatenating them together using "." as a delimiter.

[DavidSkaife]

Hi @kayogunbo 

Here is a way of doing it using Regex by defining the pattern(s) in seperate streams, then joining together and finally unioning back:

[kayogunbo]

Thanks @DavidSkaife and @Adrian_T for the wonderful solutions. Very much appreciated