cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mgp-opti
Regular Visitor

Find and extract 7 digit numbers starting with a 1 from a column containing other numbers and text

Hi all,

 

Please could someone let me know how I can extract an 7 digit number that starts with a 1 from a column which contains other numbers (luckily of a different length or which don't start with a 1) and text. It would be great to separate this number and put it in to it's own column if that's possible.


Examples below. As you can see the 7 digit number appears in different places in the column.

1002223 - 7003117328 - ID: 74526 - Attendance
1046890301 - 210438 1002515 - CW
2047074788 - 210525-1002834
1053847149 - 1016727- 212170 - cw upper

 

Many thanks in advance for any help. 
Marcus

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: Find and extract 7 digit numbers starting with a 1 from a column containing other numbers and te

OK, I had better get a kudo for this... 😜

 

PBIX is attached.

 

Column = 
    VAR __Length = LEN([Column1])
    VAR __Search1 = SEARCH("1",[Column1],1,BLANK())
    VAR __Search2 = IF(NOT(ISBLANK(__Search1)),SEARCH("1",[Column1],__Search1+1,BLANK()),BLANK())
    VAR __Search3 = IF(NOT(ISBLANK(__Search1)),SEARCH("1",[Column1],__Search2+1,BLANK()),BLANK())
    VAR __Search4 = IF(NOT(ISBLANK(__Search1)),SEARCH("1",[Column1],__Search3+1,BLANK()),BLANK())
    VAR __Search5 = IF(NOT(ISBLANK(__Search1)),SEARCH("1",[Column1],__Search4+1,BLANK()),BLANK())
    VAR __Search6 = IF(NOT(ISBLANK(__Search1)),SEARCH("1",[Column1],__Search5+1,BLANK()),BLANK())
    VAR __Search7 = IF(NOT(ISBLANK(__Search1)),SEARCH("1",[Column1],__Search6+1,BLANK()),BLANK())
    VAR __Search8 = IF(NOT(ISBLANK(__Search1)),SEARCH("1",[Column1],__Search7+1,BLANK()),BLANK())
    VAR __Search9 = IF(NOT(ISBLANK(__Search1)),SEARCH("1",[Column1],__Search8+1,BLANK()),BLANK())
    VAR __Search10 = IF(NOT(ISBLANK(__Search1)),SEARCH("1",[Column1],__Search9+1,BLANK()),BLANK())
    VAR __Numbers = { "1", "2", "3", "4", "5", "6", "7", "8", "9", "0" }
    VAR __Table = 
        ADDCOLUMNS(
            ADDCOLUMNS(
                ADDCOLUMNS(
                    FILTER(
                        { 
                            (1,__Search1), 
                            (2,__Search2), 
                            (3, __Search3), 
                            (4, __Search4), 
                            (5, __Search5), 
                            (6, __Search6), 
                            (7, __Search7), 
                            (8, __Search8), 
                            (9, __Search9), 
                            (10, __Search10)
                        },
                    NOT(ISBLANK([Value2]))
                    ),
                "__Extract",IF([Value2] + 7 > __Length,RIGHT([Column1],7),MID([Column1],[Value2],7))
                ),
                "__Eighth",IF([Value2] + 8 > __Length,RIGHT([Column1],1),MID([Column1],[Value2]+8,1))
            ),
            "__Check1",IF(ISNUMBER([__Eighth]),FALSE,TRUE),
            "__Check2",IFERROR(VALUE([__Extract]),-1),
            "__Check3",IF(SEARCH(" ",[__Extract],1,-1) = -1,TRUE,FALSE)
        )
RETURN
    MINX(
        FILTER(
            __Table,
            [__Check1] = TRUE && [__Check2] <> -1 && [__Check3] = TRUE
        ),
        [__Extract]
    )

---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

10 REPLIES 10
Super User IV
Super User IV

Re: Find and extract 7 digit numbers starting with a 1 from a column containing other numbers and te

So in the example data provided, what numbers are you looking to extract? In generaly, you are going to use a combination of SEARCH/FIND and probably MID although you might use LEFT or RIGHT as well. Depends.


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

mgp-opti
Regular Visitor

Re: Find and extract 7 digit numbers starting with a 1 from a column containing other numbers and te

Hi Greg,

 

Many thanks for your reply. I'm trying to separate 1002223, 1002515, 1002834 and 1016727 in the examples. But other them beginning with a 1 and being 7 digits long, there is no pattern. I'm hoping there is a way to select them using this criteria so that I can use these numbers (which are work orders) as unique identifiers which allow me to merge data.

 

Any advice would be fantastic.

 

Many thanks,

 

Marcus

 

Super User IV
Super User IV

Re: Find and extract 7 digit numbers starting with a 1 from a column containing other numbers and te

Hmm, that is indeed unfortunate as I cannot think how you could have made that problem any harder! I am going to give it a go however, stay tuned.


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User IV
Super User IV

Re: Find and extract 7 digit numbers starting with a 1 from a column containing other numbers and te

OK, I had better get a kudo for this... 😜

 

PBIX is attached.

 

Column = 
    VAR __Length = LEN([Column1])
    VAR __Search1 = SEARCH("1",[Column1],1,BLANK())
    VAR __Search2 = IF(NOT(ISBLANK(__Search1)),SEARCH("1",[Column1],__Search1+1,BLANK()),BLANK())
    VAR __Search3 = IF(NOT(ISBLANK(__Search1)),SEARCH("1",[Column1],__Search2+1,BLANK()),BLANK())
    VAR __Search4 = IF(NOT(ISBLANK(__Search1)),SEARCH("1",[Column1],__Search3+1,BLANK()),BLANK())
    VAR __Search5 = IF(NOT(ISBLANK(__Search1)),SEARCH("1",[Column1],__Search4+1,BLANK()),BLANK())
    VAR __Search6 = IF(NOT(ISBLANK(__Search1)),SEARCH("1",[Column1],__Search5+1,BLANK()),BLANK())
    VAR __Search7 = IF(NOT(ISBLANK(__Search1)),SEARCH("1",[Column1],__Search6+1,BLANK()),BLANK())
    VAR __Search8 = IF(NOT(ISBLANK(__Search1)),SEARCH("1",[Column1],__Search7+1,BLANK()),BLANK())
    VAR __Search9 = IF(NOT(ISBLANK(__Search1)),SEARCH("1",[Column1],__Search8+1,BLANK()),BLANK())
    VAR __Search10 = IF(NOT(ISBLANK(__Search1)),SEARCH("1",[Column1],__Search9+1,BLANK()),BLANK())
    VAR __Numbers = { "1", "2", "3", "4", "5", "6", "7", "8", "9", "0" }
    VAR __Table = 
        ADDCOLUMNS(
            ADDCOLUMNS(
                ADDCOLUMNS(
                    FILTER(
                        { 
                            (1,__Search1), 
                            (2,__Search2), 
                            (3, __Search3), 
                            (4, __Search4), 
                            (5, __Search5), 
                            (6, __Search6), 
                            (7, __Search7), 
                            (8, __Search8), 
                            (9, __Search9), 
                            (10, __Search10)
                        },
                    NOT(ISBLANK([Value2]))
                    ),
                "__Extract",IF([Value2] + 7 > __Length,RIGHT([Column1],7),MID([Column1],[Value2],7))
                ),
                "__Eighth",IF([Value2] + 8 > __Length,RIGHT([Column1],1),MID([Column1],[Value2]+8,1))
            ),
            "__Check1",IF(ISNUMBER([__Eighth]),FALSE,TRUE),
            "__Check2",IFERROR(VALUE([__Extract]),-1),
            "__Check3",IF(SEARCH(" ",[__Extract],1,-1) = -1,TRUE,FALSE)
        )
RETURN
    MINX(
        FILTER(
            __Table,
            [__Check1] = TRUE && [__Check2] <> -1 && [__Check3] = TRUE
        ),
        [__Extract]
    )

---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

Super User I
Super User I

Re: Find and extract 7 digit numbers starting with a 1 from a column containing other numbers and te

Hello @mgp-opti 

 

applying some Text.Split and List.Select should to the trick.

Here the complete solution

let
	Source = #table
	(
		{"Get Number"},
		{
			{"1002223 - 7003117328 - ID: 74526 - Attendance"},	{"1046890301 - 210438 1002515 - CW"},	{"2047074788 - 210525-1002834"},	{"1053847149 - 1016727- 212170 - cw upper"}

					}
	),
    FirstNumberWith1 = Table.AddColumn
    (
        Source,
        "First number with 1 and length = 7 splitting with - or space",
        (add)=>try List.First( List.Select(List.Transform(Text.SplitAny(add[Get Number]," -"), each Text.Remove(_,{" "})),(sel)=> Text.Start(sel,1)="1" and Text.Length(sel)=7 )) otherwise "no number with 1 found"
    )
in
    FirstNumberWith1

 

Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

mgp-opti
Regular Visitor

Re: Find and extract 7 digit numbers starting with a 1 from a column containing other numbers and te

Hi Greg,

You are a total genius - your solution works perfectly! Thank you so much, I really appreciate all the work you put in on this as I know it was a really tough one.

You fully deserve the kudo!

Massive thanks again. This Community is amazing.

Best,

Marcus

mgp-opti
Regular Visitor

Re: Find and extract 7 digit numbers starting with a 1 from a column containing other numbers and te

Hi Jimmy,

Many thanks for your response, much appreciated. However it looks like I would need to enter the row data in to the query and I've got thousands of rows.

I might  be assuming wrong!

Many thanks anyway.

Best,

Marcus

Super User I
Super User I

Re: Find and extract 7 digit numbers starting with a 1 from a column containing other numbers and te

Hello @mgp-opti 

 

no, you have to replace my Source-part with your data query and adapt the column-name to your real column.

 

Jimmy

mgp-opti
Regular Visitor

Re: Find and extract 7 digit numbers starting with a 1 from a column containing other numbers and te

Understood - sorry!

I'll give it a go and will report back.

Many thanks,

Marcus

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors