Split columns at the fourth ''/'', repeat after '';''

Hi!

I need to split a column of cells into two columns. The data in the cell of the column looks like this:

UK/H/1228/001/R/001;
UK/H/1228/002/R/001;
UK/H/1228/004/R/001;
UK/H/1228/005/R/001

I need it to split this cell into two cells, the delimiter being the fourth ''/'', but this should be repeated after the '';''. so the data should look like this:

column a:
UK/H/1228/001
UK/H/1228/002
UK/H/1228/004
UK/H/1228/005

Column b:
/R/001;
/R/001;
/R/001;
/R/001

Additionaly, if there is an instance of the data beginning with ''emea'', the delimiter must be the fifth ''/''.

Is this possible?

Thank you very much for your help.

Here, try this:

Please Login or Register  to view this content.

This would be it but all the data is in one cell.

Can it still be done? Hvala

I'm not sure I understod your question, but this is a form in which all the data is written, but it's different in every cell.

Just a 'lil bump

So, zbor's function is a good start but all the data is in one cell...

Thanks again!

And solution need to be in one cell or in multiple rows?
It's hard to see from what you write above what is one cell and what not.
Why don't you upload example workbook with your input and desired output???

Zvezek1.xlsx

This is the actual worksheet. The blue cell is the original cell, the orange cells are the desired outcome.

Thanks!

Where would "emea" appear for 5th delimiter?

In a different cell. There can't be a case where there are both types of values in the same cell.

In what cell? Can you add into your example such case too?
Made few examples that you might have (where to expect that word, what if different number of / etc etc) and add into your worksheet...

example.xlsx

This is it. Worksheet 1 is the current outline, worksheet 2 is the desired outcome.

thanks again...

Looks for me that in EMEA (EMEA/H/C/1059/II/021) is still same reulst after 4th /, not 5th:
EMEA/H/C/1059
/II/021

right?

Normally no (just realized this about that one), but still, these cases come up about once per 200 entries so I could sort it out manually...

The real problem is splitting the other cells

Here, try this code:

Please Login or Register  to view this content.

Need to watch out if there are some blank cells in I column.
use this to fix it:

Please Login or Register  to view this content.

I had a few problems with blank cells, yes. But That did it perfectly.

This really helped, thanks!

Originally Posted by Marcol

What "did it perfectly"?

He's refering probably to mine On Error Resume Next part in post #17.
I would strongly suggest him to test your functions too.