Splitting Irregular Data String into Multiple Columns

Patrick K

Hello,

I am trying to split data in one cell into 1-4 cells, depending on the number of semi-colons in the original cell. Each organization's name has a different number of characters. The organizations can appear in any order in the original cell.

Data appears as follows:

First Organization

Second Organization;Fourth Organization

First Organization;Third Organization;Fifth Organization

Fifth Organization;Fourth Organization;First Organization;Sixth Organization

I have tried multiple formulas and nothing has worked so far. Any help is greatly appreciated. Thanks in advance!

kowal

hi @Patrick K,

Have you checked those two posts?

https://community.smartsheet.com/discussion/101194/how-would-i-split-text-separated-by-commas-into-new-cells

https://community.smartsheet.com/discussion/98222/split-text-into-columns

i think both of them will help you. Hope it helps.

Patrick K

Hi @kowal,

Unfortunately these did not solve the issue. The equations in both documents don't seem to work because it is not a set number of organizations in the data string (it can be from 1-4 of them).

Any help would be appreciated. Thanks!

kowal

Hi @Patrick K,

I think the most important in your case is not how many columns you will built (i guess in your case is maximum 4) but what is the splitting sign in your case is ";" so I think you need to use the same formula but change the "-" into ";" and it shall work.

Or I am getting something wrong?

Patrick K

Hi @kowal ,

The equation breaks when there are less than 4 organizations (so less than three semi-colons) in the initial cell. If there are 4 organizations the equations work. If there are three the third column will return an "#INVALID VALUE" error and the fourth column will have the third organization in the data string.

Any suggestions would be a huge help. Thanks!

Column 1 Equation:

=LEFT([TC Name String]@row, FIND(";", [TC Name String]@row) - 1)

Column 2 Equation:

=LEFT(RIGHT([TC Name String]@row, LEN([TC Name String]@row) - FIND(";", [TC Name String]@row)), (FIND(";", RIGHT([TC Name String]@row, LEN([TC Name String]@row) - FIND(";", [TC Name String]@row) - 1))))

Column 3 Equation:

=LEFT(RIGHT(RIGHT([TC Name String]@row, (LEN([TC Name String]@row) - FIND(";", [TC Name String]@row))), LEN([TC Name String]@row) - FIND(";", [TC Name String]@row) - FIND(";", RIGHT([TC Name String]@row, (LEN([TC Name String]@row) - FIND(";", [TC Name String]@row))))), FIND(";", RIGHT(RIGHT([TC Name String]@row, (LEN([TC Name String]@row) - FIND(";", [TC Name String]@row))), LEN([TC Name String]@row) - FIND(";", [TC Name String]@row) - FIND(";", RIGHT([TC Name String]@row, (LEN([TC Name String]@row) - FIND(";", [TC Name String]@row)))))) - 1)

Column 4 Equation:

=RIGHT(RIGHT(RIGHT([TC Name String]@row, (LEN([TC Name String]@row) - FIND(";", [TC Name String]@row))), LEN([TC Name String]@row) - FIND(";", [TC Name String]@row) - FIND(";", RIGHT([TC Name String]@row, (LEN([TC Name String]@row) - FIND(";", [TC Name String]@row))))), LEN(RIGHT(RIGHT([TC Name String]@row, (LEN([TC Name String]@row) - FIND(";", [TC Name String]@row))), LEN([TC Name String]@row) - FIND(";", [TC Name String]@row) - FIND(";", RIGHT([TC Name String]@row, (LEN([TC Name String]@row) - FIND(";", [TC Name String]@row)))) - FIND(";", RIGHT(RIGHT([TC Name String]@row, (LEN([TC Name String]@row) - FIND(";", [TC Name String]@row))), LEN([TC Name String]@row) - FIND(";", [TC Name String]@row) - FIND(";", RIGHT([TC Name String]@row, (LEN([TC Name String]@row) - FIND(";", [TC Name String]@row)))))))))

Data Examples:

Original data: Organization 001;test 01;testtest 0002;000002

Column 1: Organization 001

Column 2: test 01

Column 3: testtest 0002

Column 4: 000002

Original Data: Another Organization;TEST 01;TEST 022222222222

Column 1: Another Organization

Column 2: TEST 01

Column 3: #INVALID VALUE

Column 4: TEST 022222222222