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.

4 Replies

  • 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 👍

  • 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!
  • collinsg's avatar
    collinsg
    Solution Sage
    3 years ago

    Good day Ichirag,

    Here is an approach in M for Power Query

    1. Use Text.PositionOf to find “21”.
    2. Use Text.Middle to extract the string from 21, including 7 characters in total.
    3. Convert the extracted string to a number.

    I set up data to demonstrate this,

    I then processed it with this M code.

    let
       Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
       #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Comment", type text}, {"Expected result", Int64.Type}}),
       #"Added Actual Result" = Table.AddColumn(
          #"Changed Type", 
          "Actual result", 
          each Number.From( Text.Middle( [ID], Text.PositionOf( [ID], "21" ), 7 ) ),
          Int64.Type
       ),
       #"Added Test" = Table.AddColumn(
          #"Added Actual Result", 
          "Actual result = Expected result?", 
          each [Actual result] = [Expected result], 
          type logical
       )
    in
       #"Added Test"

    Giving this result

    Hope this helps.