SharePoint List - Text to Columns

Question

Hi community

I would like to perform the function similar to the Excel Text to Columns in a SharePoint List. My Data field has for example, 1234<Branch<Section<Unit

The plan was to just use a calculated column

I would like to separate this in to 4 individual columns.

Help please?

Answer 1

Hi @Stephen E Winslett,

Thank you for posting in this community.

If you want to divide the Data field into four columns, you need to create four calculated columns.

1.In the "Column1" calculated column, please use following formula.

LEFT([Data],FIND("<",[Data])-1)

2.In the "Column2" calculated column, please use following formula.

LEFT(RIGHT([Data],LEN([Data])-FIND("<",[Data])),FIND("<",RIGHT([Data],LEN([Data])-FIND("<",[Data])))-1)

3.In the "Column3" calculated column, please use following formula.

LEFT(RIGHT(RIGHT([Data],LEN([Data])-FIND("<",[Data])),LEN(RIGHT([Data],LEN([Data])-FIND("<",[Data])))-FIND("<",RIGHT([Data],LEN([Data])-FIND("<",[Data])))),FIND("<",RIGHT(RIGHT([Data],LEN([Data])-FIND("<",[Data])),LEN(RIGHT([Data],LEN([Data])-FIND("<",[Data])))-FIND("<",RIGHT([Data],LEN([Data])-FIND("<",[Data])))))-1)

4.In the "Column4" calculated column, please use following formula.

RIGHT(RIGHT(RIGHT([Data],LEN([Data])-FIND("<",[Data])),LEN(RIGHT([Data],LEN([Data])-FIND("<",[Data])))-FIND("<",RIGHT([Data],LEN([Data])-FIND("<",[Data])))),LEN(RIGHT(RIGHT([Data],LEN([Data])-FIND("<",[Data])),LEN(RIGHT([Data],LEN([Data])-FIND("<",[Data])))-FIND("<",RIGHT([Data],LEN([Data])-FIND("<",[Data])))))-FIND("<",RIGHT(RIGHT([Data],LEN([Data])-FIND("<",[Data])),LEN(RIGHT([Data],LEN([Data])-FIND("<",[Data])))-FIND("<",RIGHT([Data],LEN([Data])-FIND("<",[Data]))))))

Here is the result of my test:

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.