Extract values from Column

[Ananthak275]

Need to extract the values between the hyphen in Column "Values". No VBA please. Trying to use the search function built in. Not sure how to how to deal with the spaces.

Values	Car	Builder	Name	Location
Green Car - Ford - Aisha - Toronto - London	Green Car	Ford	Aisha	London
Red SUV - Tesla - Cody - Madrid - Toronto	Red SUV	Tesla	Cody	Toronto
Minivan - Nissan - Tre - Paris - Toronto	Minivan	Nissan	Tre	Toronto

 

[alansidman]

A power query solution for your Windows platform.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Values", Splitter.SplitTextByDelimiter(" - ", QuoteStyle.Csv), {"Values.1", "Values.2", "Values.3", "Values.4", "Values.5"}),
    #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"Values.1", "Car"}, {"Values.2", "Builder"}, {"Values.3", "Name"}, {"Values.4", "Location"}})
in
    #"Renamed Columns"

 

[JEC]

For example:

Pull down and pull to the right

=TRIM(SUBSTITUTE(MID(SUBSTITUTE("-"&$A2,"-",REPT("@",100)),100*COLUMN(A2),100),"@",""))

 

[Rick Rothstein]

Are you deliberately skipping the 4th field in your output?