Stumped on extracting this number string

[LegoTelephone]

I have a text file that has 8 digit number strings I need to extract. The lines of interest are not evenly spaced. There are other lines in the file that start with with numbers.
The line of interest is always a row that starts with the 8 digit number, followed by two spaces, followed by a text string. Here is what I tried, but it only resulted in a column of errors and null values:

if Text.Length([ColumnWithText]) >= 11 and Text.Start([ColumnWithText], 😎 = Text.From(Number.FromText(Text.Start([ColumnWithText], 8))) and Text.Middle([ColumnWithText], 11, 2) = " " then Text.Start([ColumnWithText], 😎 else null

 Here is an example of the text output, does anyone have ideas? The example number I would want here is 95991152, and the NOT FOUND could be any text string. I really want to learn this. Thanks so much!!

ABC123X                               NAME OF REPORT I AM WORKING WITH ABC12345567810AB                               Date: 09/03/23

                                                                                                                                    
***  End of Report  ****                                                                                                            
Reported by: NAME OF COMPANYXXXXXXX                                                                                                 
ADDRESSXXXXXXXXXX                                                                                                                   
CITY, ST ZIPCODEX                                                                                                                   
ABCD# 1234567891                                                                                                                    
Phone 123-456-7891                                                                                                                  
ABC123X                               NAME OF REPORT I AM WORKING WITH ABC12345678910XX                               Date: 09/03/23

                                                                                                                                    
State/Province of STATEXX                                                                                    Page    1 of    1      
Applicant/Patient Information          Process Dates           Test Desc                 Ordering Entity                            
Ticket/Req#   Race  Sex  Birth Dt                              Lab Value                                                            
Control #   County                                             Spec Source                                                          
---------------------------------    ---------------------   ---------------------     -----------------------------                
LASTNAME, FIRST                       Collect  DT 08/04/23   TEST NAME XXXXXXXXXXXX    ORDERING NAMEXXX                             
                                      Received DT 08/09/23             RES             ORDERING COMPANYXX                           
CITYXXXXXX      AB  A2B 6A7           Process  DT 08/29/23                             ADDRESSXXXXXXXXXXXXX                         
0090455496    UNK   U   01/01/00                                                       CITY                , ST A12 8A9             
95991152   NOT FOUND                                                                                                                
                                                             BLD                       Phone 1234568910 Fax UNKNOWN                 
----------------------------------------------------------------------------------------------------------------------------------- 
***  End of Report  ****                                                                                                            
Reported by: NAME OF COMPANY XXXXXX                                                                                                 
12345 ROADLN STRT                                                                                                                   
CITYXX, ST  12345                                                                                                                   
XLIC# 12A0512345                                                                                                                    
Phone 123-456-8910                                                                                                                  
ABC123X                               NAME OF REPORT I AM WORKING WITH ABC12345678910XX                               Date: 09/03/23

                                                                                                                                    
State/Province of STATEXX                                                                                    Page    1 of    1      
Applicant/Patient Information          Process Dates           Test Desc                 Ordering Entity                            
Ticket/Req#   Race  Sex  Birth Dt                              Lab Value                                                            
Control #   County                                             Spec Source                                                          
---------------------------------    ---------------------   ---------------------     -----------------------------                
LASTNAME, FIRST                       Collect  DT 08/04/23   TEST NAME XXXXXXXXXXXX    ORDERING NAMEXXX                             
                                      Received DT 08/09/23             RES             ORDERING COMPANYXX                           
CITYXXXXXX      AB  A2B 6A7           Process  DT 08/29/23                             ADDRESSXXXXXXXXXXXXX                         
0090455496    UNK   U   01/01/00                                                       CITY                , ST A12 8A9             
95991152   NOT FOUND                                                                                                                
                                                             BLD                       Phone 1234568910 Fax UNKNOWN                 
----------------------------------------------------------------------------------------------------------------------------------- 
***  End of Report  ****                                                                                                            
Reported by: NAME OF COMPANY XXXXXX                                                                                                 
12345 ROADLN STRT                                                                                                                   
CITYXX, ST  12345                                                                                                                   
XLIC# 12A0512345                                                                                                                    
Phone 123-456-8910                                                                                                                  
ABC123X                               NAME OF REPORT I AM WORKING WITH ABC12345678910XX                               Date: 09/03/23

                                                                                                                                    
State/Province of STATEXX                                                                                    Page    1 of    1      
Applicant/Patient Information          Process Dates           Test Desc                 Ordering Entity                            
Ticket/Req#   Race  Sex  Birth Dt                              Lab Value                                                            
Control #   County                                             Spec Source                                                          
---------------------------------    ---------------------   ---------------------     -----------------------------                
LASTNAME, FIRST                       Collect  DT 08/04/23   TEST NAME XXXXXXXXXXXX    ORDERING NAMEXXX                             
                                      Received DT 08/09/23             RES             ORDERING COMPANYXX                           
CITYXXXXXX      AB  A2B 6A7           Process  DT 08/29/23                             ADDRESSXXXXXXXXXXXXX                         
0090455496    UNK   U   01/01/00                                                       CITY                , ST A12 8A9             
95991152   NOT FOUND                                                                                                                
                                                             BLD                       Phone 1234568910 Fax UNKNOWN                 
----------------------------------------------------------------------------------------------------------------------------------- 
***  End of Report  ****                                                                                                            
Reported by: NAME OF COMPANY XXXXXX                                                                                                 
12345 ROADLN STRT                                                                                                                   
CITYXX, ST  12345                                                                                                                   
XLIC# 12A0512345                                                                                                                    
Phone 123-456-8910                                                                                                                  
BRE088R                               NAME OF REPORT I AM WORKING WITH ABC12345678910XX                               Date: 09/03/23

                                                                                                                                    
State/Province of STATEXX                                                                                    Page    1 of    1      
Applicant/Patient Information          Process Dates           Test Desc                 Ordering Entity                            
Ticket/Req#   Race  Sex  Birth Dt                              Lab Value                                                            
Control #   County                                             Spec Source                                                          
---------------------------------    ---------------------   ---------------------     -----------------------------                
LASTNAME, FIRST                       Collect  DT 08/04/23   TEST NAME XXXXXXXXXXXX    ORDERING NAMEXXX                             
                                      Received DT 08/09/23             RES             ORDERING COMPANYXX                           
CITYXXXXXX      AB  A2B 6A7           Process  DT 08/29/23                             ADDRESSXXXXXXXXXXXXX                         
0090455496    UNK   U   01/01/00                                                       CITY                , ST A12 8A9             
95991152   NOT FOUND                                                                                                                
                                                             BLD                       Phone 1234568910 Fax UNKNOWN                 
----------------------------------------------------------------------------------------------------------------------------------- 
LASTNAME, FIRST                       Collect  DT 08/04/23   TEST NAME XXXXXXXXXXXX    ORDERING NAMEXXX                             
                                      Received DT 08/09/23             RES             ORDERING COMPANYXX                           
CITYXXXXXX      AB  A2B 6A7           Process  DT 08/29/23                             ADDRESSXXXXXXXXXXXXX                         
0090455496    UNK   U   01/01/00                                                       CITY                , ST A12 8A9             
95991152   NOT FOUND                                                                                                                
                                                             BLD                       Phone 1234568910 Fax UNKNOWN                       
----------------------------------------------------------------------------------------------------------------------------------- 
***  End of Report  ****                                                                                                            
Reported by: NAME OF COMPANY XXXXXX                                                                                                 
12345 ROADLN STRT                                                                                                                   
CITYXX, ST  12345                                                                                                                   
XLIC# 12A0512345                                                                                                                    
Phone 123-456-8910                                                                                                                  


[BA_Pete]

Hi @LegoTelephone ,

You could use a custom column like this to pick out your values:

List.Select(
    Text.Split([Column1], " "),
    each Text.Start(_, 1) <> "0"
    and Text.Length(Text.From(try Number.From(_) otherwise null)) = 8
)

Full example query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7ZfRcppAFIZf5Yy5c+IAJiaSuxWwZWLAWUhjmuSCkDVhasEiZpK37+6aWoMoqamSds4/KsOy7H4ue875ubqqkY6hNQ8GsF4OObPA7QK1+i71wQZyBhcuPbWdT3Bh+59hNsxhq3V03NZU0ikZzwwydgKqrqgHSvOgdrN/VSu5YyeSIPV6HcCK7yAZAmXjJM0A6rJxxyCzydkd3D6fzJ+A4Z71iXM5mGk3IMQ0qeV5g7m2Pu0KEMP2L/fB8+Gr3Tdc06oMhG93cw/kjucbXtcq4JiB9B+SmAmQBidpVIMiQa6HfzmTyHVVS/daQSb5WOnEyzih0k+TxygOmUgqnk98aztB1A/umThqYh55/A1CxuNRFAZxpvSDLGJxBnY8TNLv/CSJF4ZIk5BNJnJhJwtD+2ySgckm4dKkbnrH0ii+50kzi7LndXwSxI/CbyxTKPuxx5towFcFPPYE0InS7AHMbP1/7AW38CUYTdkb1mM9iJHEWZqMQGAYyTRez74kb8xC8JJpGr4DRYI0yiR6rrqw8obiK6965EF6xPNFhO5D16Y8zxbLSEYjFvLHZPqgthX1kEceb/YtfoeM78Hgda1wqWlREeHialn9+IPwpSxk0SOvkC8g+gxkoYPlvTqfg8zLaBmIKDmLRU9YG9LswBE5Xuj6K2heQJpLIHktldR1qyJBVFVXub861I9E07lzKn75V9UU8VHXTrda4v/l22SJJVoT2kRfBtFbuq5prSY/d1wfuu65Y244+cZ6f4rv9FZBz0sqr0GiBEE3eBLr7bgXTjFIafhuX/+QdYVdWlf5GIG6xOw5fFfTVSlt6yCzPCIja7a3qgIZ9GxDWFeitqriKLKuMtKqAUHruiy0rnmhdS0EKa9MgNa1QGhd0bpuKLSuWxJa10IQtK55ELSuaF0XhNb1jULrWgiC1jUPgtY1D4LWNQ9S7iy3LrSuxSBoXfMgaF3zINfDDrXUdpuWdEbritYVrWsOpLwyAVrXAqF1Reu6of5L64rhmwfB8M2DYPgW41QdwQ18/1wFgu+feZAP/P5Zu7n5CQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),

    addTextSelect =
    Table.AddColumn(
        Source,
        "textSelect",
        each List.Select(
            Text.Split([Column1], " "),
            each Text.Start(_, 1) <> "0"
            and Text.Length(Text.From(try Number.From(_) otherwise null)) = 8
        )
    ),
    extractListValues = Table.TransformColumns(addTextSelect, {"textSelect", each Text.Combine(List.Transform(_, Text.From)), type text})
    
in
    extractListValues

Pete

[jgeddes]

You could consider taking the first 8 characters of the row and turning that into a number. This will remove the leading zeros from any numbers. Removing the leading zeros also reduces the number of characters present. You can then test if 8 characters remain.

For example:

0090455496 is 10 characters, we will ask for the first 8 to get 00904554. Turning this into a number makes this now 904554 which is 6 characters in length.

This code will take the first 8 characters and turn them into a number. If an error occurs a null is populated. The number that we converted is then converted back into text and the length is calculated. If the length is 8 then the Column1 is returned, if not null is returned.

Hope this gets you pointed in the right direction.