Column1 | Column1.3 | |
19,BMW,G02-X4,G02 - BMW X4 xDrive20i | G02-X4 |
[SIZE=1]// Table1
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Split = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4"}),
ROC = Table.SelectColumns(Split,{"Column1.3"})
in
ROC[/SIZE]
[SIZE=1]// Table1
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Extract = Table.TransformColumns(Source, {{"Column1", each Text.BetweenDelimiters(_, ",", ",", 1, 0), type text}})
in
Extract[/SIZE]
=TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),2*LEN(A1)+1,LEN(A1)))
=MID(A1,SEARCH(",",A1,SEARCH(",",A1,1)+1)+1,SEARCH(",",A1,SEARCH(",",A1,SEARCH(",",A1,1)+1)+1)-SEARCH(",",A1,SEARCH(",",A1,1)+1)-1)
19,VOLVO,XC60,XC60 D4 Inscription 2.0LT/D 140kW 8Spd AT Wag MY19 |
19,LAND ROVER,RRS,Range Rover Sport 19MY SDV6 183kW SE AWD Auto |
19,VOLVO,XC60,XC60 T5 Inscription 2.0LT/P 187kW 8Spd AT Wag MY19 |
19,VOLKSWAGEN,Polo,Polo 70TSI Trendline 1.0L T/P 7Spd DSG 5Dr Hatch |
19,LAND ROVER,RRS,Range Rover Sport 19MY SDV6 183kW SE AWD Auto |
19,VOLVO,XC40,XC40 T4 Inscription AWD 2.0LT/P 140kW 8AT Wag MY19 |
19,VOLVO,XC60,XC60 D5 R-Design 2.0L TT/D 177kW 8Spd AT Wag MY19 |
19,LAND ROVER,Velar,Range Rover Velar 19.5MY P250 S AWD Auto |
18,BMW,F39-X2,F39 - X2 sDrive20i |
19,VOLVO,XC40,XC40 T4 Inscription AWD 2.0LT/P 140kW 8AT Wag MY19 |
Given the OP's example(s) and the location of the desired field so close to the beginning of the text, it is safe to do away with the multiple LEN function calls and use a fixed constant of sufficient size...If the string is always comma delimited, and the text to be extracted is always the 3rd column;
Try following for splitting the string in A1 cell:
Code:=TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),2*LEN(A1)+1,LEN(A1)))
Given the OP's example(s) and the location of the desired field so close to the beginning of the text, it is safe to do away with the multiple LEN function calls and use a fixed constant of sufficient size...
=TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",100)),200,100))