Forum Discussion

hamahilal's avatar
hamahilal
Copper Contributor
Jan 31, 2023

selecting specific characters (3capital letters and 4numbers from a row field in a column

Dear,
I have an renumbers only from a request how to split a specific combination of 3capital letters and 4numbers from a columns below written in red color:

2G/HUAWEI/Region5/Samawa/BSAMH01/Warkaa_SAM3994-2
2G/HUAWEI/Region5/Samawa/BSAMH01/Warkaa_SAM3994-3
2G/HUAWEI/Region5/Samawa/BSAMH01/Warkaa_SAM3994-5
2G/HUAWEI/Region5/Samawa/BSAMH01/Warkaa_SAM3994-6
2G/HUAWEI/Region5/Samawa/BSAMH01/Warkaa_SAM3994-7
2G/HUAWEI/Region5/Emarah/BEMAH02/Wshohadaa_EMA5351-1
2G/HUAWEI/Region5/Emarah/BEMAH02/Wshohadaa_EMA5351-2
2G/HUAWEI/Region5/Emarah/BEMAH02/Wshohadaa_EMA5351-3
2G/HUAWEI/Region5/Emarah/BEMAH02/Wshohadaa_EMA5351-7

NOTE:
1. there are no limit for the characters before the highlighted characters or sometimes after it.
2. The column may be too long as 1000000 records (fields or rows)
3. I need the resolve ASAP please
4. I tried many solutions over internet and YouTube without any results.

BI & Data Analysis
developer
excel
Formulas and Functions
power bi
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Jan 31, 2023

    hamahilal 

    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"
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Jan 31, 2023

    hamahilal 

    For the pattern you show the Excel formula can be extracted with the formula

    = TEXTBEFORE(
    TEXTAFTER(data,"_"),
  "-")

    That could be placed within a Lambda function to read

    = RedString(data)

    where a meaningful name for whatever it is that you have extracted would be used in place of 'RedString'.

     

    If you are importing the data then Power Query would allow you to build a more meaningful data table before it appears in Excel.

Resources