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.

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.