How do I split a text string into different ‘words’ or substrings based on a special character using a formula? For example, split the following string into a different column at each colon
Q006_23F_MCCAU_MC10_TX:67,418:67,418:67,418:HHDUP:5/11/2023:5/11/2023:NO:CLIENT FILE: MCCAU1001_DCLIST2_1558.CSV
I don’t need every field, but I need to grab some of the middle fields, and haven’t gotten the right/left commands to work well with the search function.
This is tough, especially with so many instances of your separator.
It doesn’t seem like a particularly good use case for monday.com. However… it is possible to achieve.
I’d strongly suggest using some helper columns to organize this more effectively:
Second Colon POS formula:
SEARCH(
":",
RIGHT(
{Text},
LEN({Text})-{First Colon POS}
)
)+{First Colon POS}
Third String formula:
LEFT(
RIGHT(
{Text},
LEN({Text})-{Second Colon POS}
),
{Third Colon POS}-{Second Colon POS}-1
)
That should give you enough to get you going. But again I’d really stress this isn’t a typical use case for monday.com and not an area where I’d consider it to be particularly effective.
EDIT: Of course you’ll need to increment the various elements of these formulae to get different strings throughout the name.
This I can work with!! Thank you @FrancisElliott! I hadn’t thought of creating additional columns to make it work.
Hi I’m sorry but I don’t understand this solution could you explain me wy there are so many column and what is the formula for all column please ?
@steph68 I have a really long text string that I want to split up into at least 7 parts - always on a colon (:). Since there are few text functions available in Monday, the solution is to split the string in increments.
So the first column would be the whole string. The next column would use the first formula Francis provided. This would split the first column into two more columns - the first left most ‘word’, then the rest of the string. Then you split the “rest of the string” into two more columns. You keep doing this until you have everything split up.
Hi, have you got an exemple for me please ?
I don’t have any code yet beyond what Francis provide. A sample of the text string I have to break up is in my first posting. Were you looking for a specific solution to a different problem?