finding substring from a string with dashes

[Jeffrey Green]

I have parts in the following format:

[TABLE="width: 352"]
<tbody>[TR]
[TD="class: xl64, width: 352"]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[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 352"]Thanks
Jeff[/TD]
[/TR]
</tbody>[/TABLE]

 

[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]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]source[/td][td][/td][td=bgcolor:#70AD47]result[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]ABVD-JtU5d-NR393[/td][td][/td][td=bgcolor:#E2EFDA]ABVD-JtU5d-N[/td][/tr]
[/table]

 

[AlanY]

Re: findiog substring from a string with dashes

try

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