Forum Discussion
Stumped on extracting this number string
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 nullHere 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
2 Replies
Hi Anonymous ,
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 extractListValuesPete
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.
