Solved: Find and extract 7 digit numbers starting with a 1... - Page 2

mgp-opti · ‎02-26-2020

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

Greg_Deckler · ‎02-26-2020

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

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

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024