Split text in a cell into multiple columns

[excelvbanoob420]

Hello,

I'm trying to solve a difficult problem of splitting data in a cell into multiple columns based on the number of items in that cell. Below is the sample data and I would like to split the data in Column3, Column4 and Column5 into multiple columns where each column contains an individual values based on text string 'FJ-SJ'. Any text with 'FJ-RJ' should be ignored in the split. This problem can better be understood by looking at the input and desired output shown below.

Input:

Column1	Column2	Column3	Column4	Column5
FJ-SJ-1111-1111	Randomtext1	FJ-SJ-0000-0101 FJ-RJ-0000-0010	FJ-SJ-0000-0102 FJ-SJ-0000-0103 FJ-SJ-0000-0104	FJ-SJ-0000-1111 FJ-RJ-0000-1111
FJ-SJ-1111-1112	Randomtext2	FJ-SJ-0000-0112 FJ-SJ-0000-0113 FJ-SJ-0000-0114	FJ-RJ-0000-0011 FJ-SJ-0000-0106 FJ-SJ-0000-0105	FJ-SJ-0000-1112 FJ-SJ-0000-1113 FJ-SJ-0000-1114
FJ-SJ-1111-1113	Randomtext3	FJ-RJ-0000-0011 FJ-RJ-0000-0012 FJ-SJ-0000-0115 FJ-SJ-0000-0116 FJ-SJ-0000-0117	FJ-RJ-0000-0011 FJ-RJ-0000-0012 FJ-SJ-0000-0107 FJ-SJ-0000-0108	FJ-RJ-0000-1112 FJ-SJ-0000-1115 FJ-SJ-0000-1116 FJ-SJ-0000-1117
FJ-SJ-1111-1114	Randomtext4	FJ-SJ-0000-0118 FJ-SJ-0000-0119	FJ-SJ-0000-0109	FJ-SJ-0000-1119

Output:

Column1

Column2

Column3

Column4

Column5

FJ-SJ-1111-1111

Randomtext1

FJ-SJ-0000-0101 FJ-RJ-0000-0010

FJ-SJ-0000-0101

FJ-SJ-0000-0102 FJ-SJ-0000-0103 FJ-SJ-0000-0104

FJ-SJ-0000-0102

FJ-SJ-0000-0103

FJ-SJ-0000-0104

FJ-SJ-0000-1111 FJ-RJ-0000-1111

FJ-SJ-0000-1111

FJ-SJ-1111-1112

Randomtext2

FJ-SJ-0000-0112 FJ-SJ-0000-0113 FJ-SJ-0000-0114

FJ-SJ-0000-0112

FJ-SJ-0000-0113

FJ-SJ-0000-0114

FJ-RJ-0000-0011 FJ-SJ-0000-0106 FJ-SJ-0000-0105

FJ-SJ-0000-0106

FJ-SJ-0000-0105

FJ-SJ-0000-1112 FJ-SJ-0000-1113 FJ-SJ-0000-1114

FJ-SJ-0000-1112

FJ-SJ-0000-1113

FJ-SJ-0000-1114

FJ-SJ-1111-1113

Randomtext3

FJ-RJ-0000-0011 FJ-RJ-0000-0012 FJ-SJ-0000-0115 FJ-SJ-0000-0116 FJ-SJ-0000-0117

FJ-SJ-0000-0115

FJ-SJ-0000-0116

FJ-SJ-0000-0117

FJ-RJ-0000-0011 FJ-RJ-0000-0012 FJ-SJ-0000-0107 FJ-SJ-0000-0108

FJ-SJ-0000-0107

FJ-SJ-0000-0108

FJ-RJ-0000-1112 FJ-SJ-0000-1115 FJ-SJ-0000-1116 FJ-SJ-0000-1117

FJ-SJ-0000-1115

FJ-SJ-0000-1116

FJ-SJ-0000-1117

FJ-SJ-1111-1114

Randomtext4

FJ-SJ-0000-0118 FJ-SJ-0000-0119

FJ-SJ-0000-0118

FJ-SJ-0000-0119

FJ-SJ-0000-0109

FJ-SJ-0000-0109

FJ-SJ-0000-1119

FJ-SJ-0000-1119

Any help i can get to solve this dynamic column creation problem will be highly appreciated.

 

[JamesCanale]

=FILTER(TEXTSPLIT(C2,CHAR(10)),LEFT(TEXTSPLIT(C2,CHAR(10)),5)<>"FJ-RJ")

 

[excelvbanoob420]

=FILTER(TEXTSPLIT(C2,CHAR(10)),LEFT(TEXTSPLIT(C2,CHAR(10)),5)<>"FJ-RJ")

This solution only gives the first value of every cell. So its not what I am looking for.
Secondly, this will need a VBA code to solve.

 

[JamesCanale]

This gave me every item in the original cell separately (provided it doesn't start with "FJ-RJ"). And no VBA was used.

 

[excelvbanoob420]

This gave me every item in the original cell separately (provided it doesn't start with "FJ-RJ"). And no VBA was used.

How?

 

[JamesCanale]

I use textsplit to split up the string whenever it finds a CHAR(10), which is the line break. But if it's not working use the CODE function on every character in the string to find what code is being used that you want to split by.

=CODE(MID(A1,SEQUENCE(LEN(A1)),1))

That should give you what CHAR you are looking for to split by.

 

[excelvbanoob420]

I use textsplit to split up the string whenever it finds a CHAR(10), which is the line break. But if it's not working use the CODE function on every character in the string to find what code is being used that you want to split by.

=CODE(MID(A1,SEQUENCE(LEN(A1)),1))

That should give you what CHAR you are looking for to split by.

ok that works for one column. However if you look at my the output i provided in my original post, I have multiple columns that i want to split. Is there a way I can do this recursively for each column?

 

[JamesCanale]

Just put that formula next to each column?

 

[Anthony47]

@excelvbanoob420
In case the problem was not solved using the formula provided by JamesCanale, then it would be better if you share a sample workbook with the real data and cell formats.
You need to use a fileshare service; Microsoft OneDrive, Google Drive, Dropbox and many other companies offer this option.

 

[Fluff]

@Anthony47 what's wrong with the sample data the OP has already provided?