How to separate data?

Saiyanite

New Member
Joined
Oct 14, 2002
Messages
6
Hi,

I'm trying to work out how to separate data into differing units to be placed into different columns.

For example:
Separate GBDHM/GBYRK/GBLDS/GBDHM/GBYRK/GBLDS/GBDHM

into

GBDHM/GBYRK, GBYRK/GBLDS, GBLDS/GBDHM, GBDHM/GBYRK, GBYRK/GBLDS, GBLDS/GBDHM.

to be then split into different columns.

Currently I am going through a tedious process of using =left() and =right() formulae to get these sorted. Can anyone advise if an easier solution might be available?

Also note this example is not a definite amount of values.

It could be GBDHM/GBYRK/GBLDS/GBDHM or GBDHM/GBYRK/GBLDS/GBDHM however it will always have a minimum of 11 characters such as GBDHM/GBYRK.

Many thanks.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Have you tried Data > Text to Columns, specifying / as the delimiter?
 

klb

Well-known Member
Joined
Apr 3, 2002
Messages
821
So I am clear on this, except for the first and last set of 5, each set of five will be the second part in one cell and also the first part in the next cell?
 

Saiyanite

New Member
Joined
Oct 14, 2002
Messages
6
VoG II, I have tried that and unfortunately it doesn't provide what I need.

I am hoping to grab each set of 5 to the left and right of each / and place that set of 11 characters into a separate cell.

If it was only a short list of entries I would manually handle it however the spreadsheet has in excess of 2500 rows of data to separate so am obviously trying to simplify this, and if it can be done more efficiently than they way I currently am, its a bonus n will save me a lot of time.

Thanks.

Cheers.
 

klb

Well-known Member
Joined
Apr 3, 2002
Messages
821
Then I would go with VoG II suggestion of first separating all of the 5 letter groups into separate cells. Second, I would then combine them with
a formula.

If the last cell of data is in column K, my first formula would be:
=if(isblank(m1),"",L1&"/"&M1). Copy this over as many columns as you need. The do a copy, paste special, values to get rid of the formulas.
 

Forum statistics

Threads
1,181,361
Messages
5,929,531
Members
436,676
Latest member
Mavri

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