Using Multiple 'Substitute' in one formula

[JimmyBos]

Hello All,

once more, and it looks like the last formula i would like to 'perfect' contains multiple 'Substitute'. Any idea how to change the formula into a bit easier to read?

Formula as it is:

Project clean = LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SELECTEDVALUE(POStuklijst[ParentProject])," ",""),"A",""),"B",""),"C",""),"D",""),"E",""),"F",""),"G",""),"H",""),"I",""),"J",""),"K",""),"L",""),"M",""),"N",""),"O",""),"P",""),"Q",""),"R",""),"S",""),"T",""),"U",""),"V",""),"W",""),"X",""),"Y",""),"Z",""),7)

[Cookistador]

Hello @JimmyBos 

Does the following snippet help your ? (each time you have to remove letter or character, Unicode is a pretty good alternative)

Project clean =

VAR SourceText = SELECTEDVALUE('Table (2)'[Project])

VAR TextLength = LEN(SourceText)

VAR _Sequence = GENERATESERIES(1, 8)

VAR FilteredCharacters =

    FILTER(

        _Sequence,

        VAR CurrentChar = MID(SourceText, [Value], 1) 

        VAR CharCode = UNICODE(CurrentChar) // Get Unicode value of the character

        RETURN

            NOT(

                CharCode = 10 // Exclude space (Unicode 32)

                || (CharCode >= 65 && CharCode <= 90) // Exclude A-Z (Unicode 65-90)

            )

    )

VAR CleanedString =

    CONCATENATEX(

        FilteredCharacters,

        MID(SourceText, [Value], 1),

        "",

        [Value],

        ASC

    )

RETURN

LEFT(CleanedString, 7)

[JimmyBos]

Hello @Cookistador , Thank you for showing me this formula. There are 2 small adjustments needed. The first issue is, when i add this formula to my visual, many extra rows pop up. There should be a line added like: 

'IF( NOT(ISBLANK(???)),' I tried to get it working, but i do not know what to place on the '???'. The second is this formula has a result of 6 characters. There should be 7. (changing the '7' in your formula to '8' did not change the output.

[Cookistador]

Glad to be helpful

This dax Code should solve this issue

Project clean =

VAR SourceText = SELECTEDVALUE('Table (2)'[Project])

VAR TextLength = LEN(SourceText)

VAR _Sequence = GENERATESERIES(1, 100)

VAR FilteredCharacters =

    FILTER(

        _Sequence,

        VAR CurrentChar = MID(SourceText, [Value], 1) // Get character at current position

        VAR CharCode = UNICODE(CurrentChar) // Get Unicode value of the character

        RETURN

            NOT(

                CharCode = 10 // Exclude space (Unicode 32)

                || (CharCode >= 65 && CharCode <= 90) // Exclude A-Z (Unicode 65-90)

            )

    )

VAR CleanedString =

    CONCATENATEX(

        FilteredCharacters,

        MID(SourceText, [Value], 1),

        "",

        [Value],

        ASC

    )

RETURN

IF(LEN(LEFT(CleanedString, 7)) = 0, BLANK(),LEFT(CleanedString, 7))

This is what I got without the if

And the reuslt with the IF

Just be careful that a space count for one character, so if you have something like

HELLO WORLD, you would have a blank line, due to the space

[JimmyBos]

Hello @Cookistador , This fixed both issues, thanks! Just noticed there small issue left.

First i would like to show you what i am filtering with the formula you gave me. Everything related to a project we name something like PR2500042.01 or PR2500042.01 or I2500042 or PRVH2500042. All belong to project 2500042. The formula will tell me which project something is related to. See screenshot below.

If for example PR2500042.01 was written with small letters (pr2500042.01) i would still like to get the same outcome: 2500042. Would your formula give me the same outcome?

[Cookistador · Accepted solution]

I see,

it would be easier to do it in the other way, saying you only need the numerical value

But to continue on the same approach, you just have to add 
CharCode >= 97 && CharCode <= 122) to exclude the lowercase letter

Project clean =

VAR SourceText = SELECTEDVALUE('Table (2)'[Project])

VAR TextLength = LEN(SourceText)

VAR _Sequence = GENERATESERIES(1, 100)

VAR FilteredCharacters =

    FILTER(

        _Sequence,

        VAR CurrentChar = MID(SourceText, [Value], 1) // Get character at current position

        VAR CharCode = UNICODE(CurrentChar) // Get Unicode value of the character

        RETURN

            NOT(

                CharCode = 10 // Exclude space (Unicode 32)

                || (CharCode >= 65 && CharCode <= 90) // Exclude A-Z (Unicode 65-90)

                || (CharCode >= 97 && CharCode <= 122) // Exclude a-z (Unicode 97-122)

            )

    )

VAR CleanedString =

    CONCATENATEX(

        FilteredCharacters,

        MID(SourceText, [Value], 1),

        "",

        [Value],

        ASC

    )

RETURN

IF(LEN(LEFT(CleanedString, 7)) = 0, BLANK(),LEFT(CleanedString, 7))

[JimmyBos]

@Cookistador  Thanks a lot for all the help, all works perfect!