finding substring from a string with dashes

Jeffrey Green

Well-known Member
Joined
Oct 24, 2007
Messages
1,017
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>
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,320
Re: findiog substring from a string with dashes

try PowerQuery

Code:
[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]
sourceresult
ABVD-JtU5d-NR393ABVD-JtU5d-N
 
Last edited:

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
3,981
Office Version
365, 2019, 2016
Platform
Windows
Re: findiog substring from a string with dashes

try

=LEFT(A1,FIND("-",A1,FIND("-",A1)+1)+1)
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,122
Messages
5,466,824
Members
406,500
Latest member
Tknotmaxx

This Week's Hot Topics

Top