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.

grazitti_sapna's avatar
grazitti_sapna
Super User
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.

lchirag's avatar
lchirag
Frequent Visitor
3 years ago
Hi Sapna, Thank you very much for the solution, this was simple and worked perfectly! Appreciate you help, thanks a lot!