How to split text string in

aanaduta

New Member
Joined
Sep 2, 2013
Messages
12
Hi Awesome people,
I need help please. What the formulato use if I wanted to split text. For example
19,BMW,G02-X4,G02 - BMW X4 xDrive20i I wanted to use formula just to pick up G02-X4?

Thanks
AA

<colgroup><col></colgroup><tbody>
</tbody>
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
you can try PowerQuery (Get&Transform)

Column1Column1.3
19,BMW,G02-X4,G02 - BMW X4 xDrive20iG02-X4

Code:
[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

Code:
[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]
 
Last edited:
Upvote 0
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)))

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:
Code:
=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)
 
Upvote 0
Other examples below, thank you

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

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>
 
Upvote 0
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))
 
Upvote 0
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!
 
Upvote 0
Just putting in a very late response. Would the Flash Fill feature in Excel 2013 also achieve the same results in this particular case?
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

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
Back
Top