Extracting specific numeric pattern from text field

[lchirag]

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!

[collinsg]

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.

[AjithPrasath]

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 · Accepted solution]

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]

Hi Sapna, Thank you very much for the solution, this was simple and worked perfectly! Appreciate you help, thanks a lot!