Forum Discussion

lchirag's avatar
lchirag
Frequent Visitor
3 years ago
Solved

Extracting specific numeric pattern from text field

Hi There, I seek logic to extract a 7-digit numerical value from the text field starting with the digit "21". It can be anywhere in the text field and there can be other digits too. Please help!
Need Help
  • grazitti_sapna's avatar
    grazitti_sapna
    3 years ago

    Hi lchirag 

    You can try using by a calculated column 

    Extracted Value =
    VAR TextValue = 'YourTable'[YourTextField] // Replace 'YourTable' with your actual table name and 'YourTextField' with the name of your text field column
    VAR StartIndex = FIND("21", TextValue, 1) // Find the index where "21" starts in the text
    VAR ExtractedText = MID(TextValue, StartIndex, 7) // Extract the next 7 characters starting from the "21" index
    VAR ExtractedValue = VALUE(ExtractedText) // Convert the extracted text to a numerical value
    RETURN IF(LEN(ExtractedText) = 7 && StartIndex > 0 && ISNUMBER(ExtractedValue), ExtractedValue)

     

    This formula uses the FIND function to locate the starting index of "21" in the text field. Then, it uses the MID function to extract the next 7 characters from that starting index. The VALUE function is applied to convert the extracted text into a numerical value.

     

    Thank you 

    Hope this will help you.

AjithPrasath's avatar
AjithPrasath
Resolver II
3 years ago

Hi lchirag ,

 Please try the below code. Here 'YourTableName'[TextField] is the column from which you are extracting the data.

ExtractedValue =
VAR TextValue = 'YourTableName'[TextField]
RETURN
    IF(
        ISBLANK(TextValue),
        BLANK(),
        MID(
            TextValue,
            SEARCH("21", TextValue, 1),
            7
        )
    )

 

 

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍