SharePoint List - Text to Columns

Stephen E Winslett 20 Reputation points
2023-07-23T23:17:02.77+00:00

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?

SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
11,175 questions
0 comments No comments
{count} votes

Accepted answer
  1. Ling Zhou_MSFT 21,245 Reputation points Microsoft Vendor
    2023-07-24T06:12:06.67+00:00

    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:

    enter image description here


    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.

    1 person found this answer helpful.
    0 comments No comments

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.