finding substring from a string with dashes

Jeffrey Green

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

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497
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
4,243
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Re: findiog substring from a string with dashes

try

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

Forum statistics

Threads
1,141,204
Messages
5,704,951
Members
421,372
Latest member
Jamie11

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top