cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

## 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.

1 ACCEPTED SOLUTION
Resolver I

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

4 REPLIES 4
Super User

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.

Resolver II

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
)
)``````

Resolver I

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

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

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors