Split text from a column into 3 or more parts into different columns

Platform discussions
,
1

Hello,

Here is my request for help, I would like the text in the column “description” to be divided as many times as there are commas.
For example if in the description column it says: “bed, table”.
One column will receive the word “bed” and another column the word “table”.
I searched this forum for solutions to my problem and I found this which is quite close to the solution I am looking for:

Column 1 : IF(SEARCH(“,”, {Description}&“,”)<LEN({Description}), LEFT({Description}, SEARCH(“,”, {Description}&“,”)-1), {Description})

Column 2: IF(SEARCH(“,”, {Description}&“,”)<LEN({Description}), MID({Description}, SEARCH(“,”, {Description}&“,”)+1,9999), “”)

The problem is that I can’t apply and modify this formula if there is a third word or even a fourth (so 2 more columns)

Can someone please help me?

1 Like
2

Hi Stephane,

I solved something similar in this post:

Perhaps you can take this approach. Good Luck!

3

Hello, yes I have seen your solution, but I do not know why it does not work, I have adapted to my columns:
SEARCH(
“,”,
RIGHT(
{Description},
LEN({Description})-{produit 1}
)
)+{produit 1}

image
image635×633 38 KB

4

I’m sorry if I don’t understand your solution there’s definitely something I’m doing wrong, or I don’t understand your approach and way of doing this formula…

5

Hi Stephane,

I can see that you’re new to using formulae. What’s going wrong here is that you need to create a “First Comma Pos” column using a much more simple formula:

SEARCH(
    ",",
    {Description}
)

Then you need to create a column called “Second Comma Pos” like so

SEARCH(
    ",",
    RIGHT(
        {Description},
        LEN({Description})-{First Comma Pos}
    )
)+{First Comma Pos}

Increment this by creating new columns and replacing each new “Pos” (position) in the formula for as many strings as you’d like to detect. These columns are just getting the positions of each comma.

The next columns you create will isolate the strings by using those positions.

Your first string formula will be very simple:

LEFT(
    {Text},
    {First Comma POS}-1
)

Then you need to use this more complex formula for further strings. This would be the third string:

LEFT(
    RIGHT(
        {Text},
        LEN({Text})-{Second Comma POS}
    ),
    {Third Comma POS}-{Second Comma POS}-1
)

And naturally you need to change “Second” and “Third” just up or down depending on which string you need. Create as many columns as you like and change these values to get the right string.

I should stress though that if you don’t have a fundamental understanding of how formulae work, you’re likely to consistently struggle. It would probably be wise to reach out to an expert for help. I’m available and monday.com can easily point you in the direction of many competent partners.

6

Ok now I understand your Formula, Bu t I don’t understand why sometimes, there are a number and sometimes no…

image
image983×428 37.4 KB

It’s the same formula but it doesn’t work every time

7

If there is no comma, there is no number. What would you expect to see?

8

Sorry it’s not the good screenshot.
Here you can see the good

image
image1564×394 38.7 KB

Like you can see, in red I have words before comma and after comma, but nothing is write on my second column…
In green, There are 3 word, but in my third column le word is not completed…

9

It’s not working because there is no comma at the end of your string.

So one approach here would be to create a helper formula column. Let’s call it “Formula Description”.

We’re going to add a comma at the end using the concatenate function, so there will always be a comma at the end.

Here’s the formula for “Formula Description”:

CONCATENATE({Description},",")

Now in all your other formulae, replace {Description} with {Formula Description}.

10

I understand and it’s near perfect, but for the third and Fourth column I’ve got a problem. Like you can see , in the Third column the word is not complete and the other part is in the fourth column. And when I’ve got this problem le fourth word is not complete also.

image
image862×59 4.46 KB

11

Please share:

  1. Your “Third Comma POS” column formula.
  2. Your “Third Product” column formula.

These are almost certainly incorrectly configured.

12

Third comma POS:
SEARCH(
“,”,
LEFT(
{Formula Description},
LEN({Formula Description})-{Second Comma Pos}
)
)+{Second Comma Pos}

Third Product :
LEFT(
RIGHT(
{Formula Description},
LEN({Formula Description})-{Second Comma Pos}
),
{Third Comma Pos}-{Second Comma Pos}-1
)

13

Looks like you’re using the LEFT function in your Comma Pos columns. It should be RIGHT. Double check all of those.

Here is the corrected formula for “Third Comma Pos”

SEARCH(
    ",",
    RIGHT(
        {Formula Description},
        LEN({Formula Description})-{Second Comma Pos}
    )
)+{Second Comma Pos}
14

Yes it’s true , I’m really sorry
Thank you to have taked the time with me…

1 Like
15

Great to hear it’s working! Feel free to reach out if you ever want some professional assistance with your monday.com solution.

1 Like
17

Hi Francis, I’m currently in need of a similar solution (I think) & likely need professional assistance. What’s the best way to reach out?