Forum Discussion
selecting specific characters (3capital letters and 4numbers from a row field in a column
Power Query
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Data", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, true), {"Data.1", "Data.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Data.1", type text}, {"Data.2", type text}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Data.2", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, false), {"RedString", "Data.2.2"}),
#"Removed Other Columns" = Table.SelectColumns(#"Split Column by Delimiter1",{"RedString"})
in
#"Removed Other Columns"https://docs.google.com/spreadsheets/d/1HY2AHOEFPZLYnJP4Vm8E47W_4Gcd0ZmS/edit?usp=share_link&ouid=109930702746120690068&rtpof=true&sd=truePeterBartholomew1
dear Mr. Peter
I do appreciated your work but after some editing to what you sent i got after loading the data to a table on the workbook (as you know filtering of result in power query will show not more than 1000 record) so when filtering the table will show individual numbers from 1-7 and A3 and three null (blank) records and for extra note i shall sent you the excel file containing the excel sheet to try you solution on it and send me back your new solution according to live result as you will see.
many thanks for your support in advance
It turns out that 233 records did not contain the pattern of a final underscore followed by a hyphen that I was looking for. The options are to clean the data or look for a more general set of search criteria, possibly involving Regular Expressions.
PeterBartholomew1 dear Mr. Peter
An tthat's the issue I have hope you can find me a good resolve for this issue.
I would be appreciate your time and effort in advance.
