Forum Discussion

JimmyBos's avatar
JimmyBos
Helper II
1 year ago
Solved

Using Multiple 'Substitute' in one formula

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 ...
Need Help
Show and Tell
  • Cookistador's avatar
    Cookistador
    1 year ago

    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))
Cookistador's avatar
Cookistador
Super User
1 year ago

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's avatar
JimmyBos
Helper II
1 year ago

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