finding substring from a string with dashes

[Jeffrey Green]

I have parts in the following format:

ABVD-JtU5d-NR393. Where the prefix, base and suffix can be any length., but there are always two dashes. I need to add a column to show the prefix, base, and first letter only of suffix. So for the above part, i want to extract ABVD-JtU5d-N

Thanks Jeff

<tbody>
</tbody>

 

[sandy666]

Re: findiog substring from a string with dashes

try PowerQuery

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "source", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, true), {"source.1", "source.2"}),
    #"Extracted First Characters" = Table.TransformColumns(#"Split Column by Delimiter", {{"source.2", each Text.Start(_, 1), type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Extracted First Characters",{"source.1", "source.2"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"result")
in
    #"Merged Columns"[/SIZE]

source		result
ABVD-JtU5d-NR393		ABVD-JtU5d-N

 

[AlanY]

Re: findiog substring from a string with dashes

try

=LEFT(A1,FIND("-",A1,FIND("-",A1)+1)+1)