Forum Discussion

mwild's avatar
mwild
Frequent Visitor
4 years ago
Solved

String Extract based on criteria

I have a table and one column contains free text.  I'm looking to add a column which will search the free text and extract anything which has a letter followed by 5 numbers.   For example - the bel...
Need Help
  • moizsherwani's avatar
    moizsherwani
    4 years ago

    mwild  ***Made a tweak to the code***

    The following works. I will let you tweak the code further if you need to so I am not doing all your homework ðŸ˜‚

     

    StringMatch = 
VAR NoBlankFreeText =
    SUBSTITUTE ( 'Sample Data'[FreeText], " ", "", 1 )
VAR UpperD =
    FIND ( "D", NoBlankFreeText, 1, BLANK () )
VAR LowerD =
    FIND ( "d", NoBlankFreeText, 1, BLANK () )
VAR PositionD =
    IF ( ISNUMBER ( UpperD ), UpperD, LowerD)
VAR CheckNumbers =
    ISERROR (
        ISNUMBER ( VALUE ( MID ( NoBlankFreeText, PositionD + 1, 5 ) ) )
    )
RETURN 
    IF (
        NOT ( CheckNumbers ),
        MID ( NoBlankFreeText, PositionD, 6),
        "No Match"
    )

     

     

     

     

mwild's avatar
mwild
Frequent Visitor
4 years ago

The letter will always be "D" or "d"
There may be a space (or may not be) and then 5 numbers between 0 and 9

moizsherwani's avatar
moizsherwani
Continued Contributor
4 years ago

mwild  ***Made a tweak to the code***

The following works. I will let you tweak the code further if you need to so I am not doing all your homework ðŸ˜‚

 

StringMatch = 
VAR NoBlankFreeText =
    SUBSTITUTE ( 'Sample Data'[FreeText], " ", "", 1 )
VAR UpperD =
    FIND ( "D", NoBlankFreeText, 1, BLANK () )
VAR LowerD =
    FIND ( "d", NoBlankFreeText, 1, BLANK () )
VAR PositionD =
    IF ( ISNUMBER ( UpperD ), UpperD, LowerD)
VAR CheckNumbers =
    ISERROR (
        ISNUMBER ( VALUE ( MID ( NoBlankFreeText, PositionD + 1, 5 ) ) )
    )
RETURN 
    IF (
        NOT ( CheckNumbers ),
        MID ( NoBlankFreeText, PositionD, 6),
        "No Match"
    )