How to split text string in

[aanaduta]

Hi Awesome people,
I need help please. What the formulato use if I wanted to split text. For example [TABLE="width: 490"]
<colgroup><col></colgroup><tbody>[TR]
[TD]19,BMW,G02-X4,G02 - BMW X4 xDrive20i I wanted to use formula just to pick up G02-X4?

Thanks
AA
[/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

Are there other examples you have too?

 

[sandy666]

you can try PowerQuery (Get&Transform)

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]19,BMW,G02-X4,G02 - BMW X4 xDrive20i[/td][td][/td][td=bgcolor:#E2EFDA]G02-X4[/td][/tr]
[/table]

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

or

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

 

[smozgur]

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:

=TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),2*LEN(A1)+1,LEN(A1)))

This is simply replacing the delimiter with spaces in length of the entire string (to provide enough padding for trim after mid), then take the required section with spaces around, then trim it to remove spaces to get the actual string. A bit tricky but it works.
Note: 2 in the formula is "Required Column Number - 1", so it finds the 3rd column.

Less beautiful search version for the same assumption:

=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)

 

[aanaduta]

Other examples below, thank you

[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]19,VOLVO,XC60,XC60 D4 Inscription 2.0LT/D 140kW 8Spd AT Wag MY19[/TD]
[/TR]
[TR]
[TD]19,LAND ROVER,RRS,Range Rover Sport 19MY SDV6 183kW SE AWD Auto[/TD]
[/TR]
[TR]
[TD]19,VOLVO,XC60,XC60 T5 Inscription 2.0LT/P 187kW 8Spd AT Wag MY19[/TD]
[/TR]
[TR]
[TD]19,VOLKSWAGEN,Polo,Polo 70TSI Trendline 1.0L T/P 7Spd DSG 5Dr Hatch[/TD]
[/TR]
[TR]
[TD]19,LAND ROVER,RRS,Range Rover Sport 19MY SDV6 183kW SE AWD Auto[/TD]
[/TR]
[TR]
[TD]19,VOLVO,XC40,XC40 T4 Inscription AWD 2.0LT/P 140kW 8AT Wag MY19[/TD]
[/TR]
[TR]
[TD]19,VOLVO,XC60,XC60 D5 R-Design 2.0L TT/D 177kW 8Spd AT Wag MY19[/TD]
[/TR]
[TR]
[TD]19,LAND ROVER,Velar,Range Rover Velar 19.5MY P250 S AWD Auto[/TD]
[/TR]
[TR]
[TD]18,BMW,F39-X2,F39 - X2 sDrive20i[/TD]
[/TR]
[TR]
[TD]19,VOLVO,XC40,XC40 T4 Inscription AWD 2.0LT/P 140kW 8AT Wag MY19[/TD]
[/TR]
</tbody>[/TABLE]

 

[aanaduta]

It works , thanks everone

 

[sandy666]

You are welcome

Have a nice day

 

[Rick Rothstein]

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:

=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))

 

[smozgur]

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))

I agree, Rick. Thanks for the nice touch!

 

[mrpavlos]

Just putting in a very late response. Would the Flash Fill feature in Excel 2013 also achieve the same results in this particular case?