Split text to columns

[Gabriel_Aldamiz]

Hi everyone, I’m new here, and haven’t found the response to my problem anywhere.

One of my columns contains text with several tags. I need to split the text in that column, into several columns in which each column would contain one tag.

Can anyone help with that?

Thanks!!

[Justin_Barrett]

Thanks for the breakdown. Just to make sure I’m clear, you want to start with a field containing something like this:

Sunday 7:30 AM Monday 7:30 AM Tuesday 7:30 AM Wednesday 7:30 AM Saturday 8:00 PM

…and use a formula to give you this:

Sunday Monday Tuesday Wednesday Saturday

Is that correct?

If so, here’s a follow-up question: will there ever be a case where a given weekday will repeat? For example, could there be more than one meeting on Sunday? If so, would you want to see “Sunday Sunday” or just “Sunday”?

Another question: could these meetings take place at virtually any time (i.e. any time between, say, 7 am and 10 pm), or do they only occur in more distinct blocks (e.g. between 7 and 10 am, and between 7 and 10 pm)?

[Jesse_Bayer]

Hi Justin, your first question gave me some pause regarding the future of this field. We currently have set no limit on the amount of entries for this field; to answer you question, the potential permutations for weekdays is 10080. This would never happen, but theoretically every minute of every day * 7 days a week. I’ve looked at how other similar sites have handled this and they have limited the initial meeting entry to only 7 options. Even still, we allow for “updates” to an existing entry and, again, there is no limit. :man_shrugging: Regarding time “blocks” there are none. It’s only start time.

[Justin_Barrett]

Thanks for the update. I was hoping to find a more defined pattern that would make the formula easier to create, but unfortunately I don’t see that based on your description.

Jesse_Bayer:

Regarding time “blocks” there are none. It’s only start time.

I think you misunderstood my question. I wasn’t asking about defining time blocks for the events. I was asking if the events could start at any time of day, or if you’re only allowing them to start within certain windows of time (windows is probably a better word than blocks for this case). The reason that I asked about time windows is because the method I’d use involves systematically removing specific pieces like numbers. If the event start times are only allowed within certain ranges—e.g. an event can’t start earlier than 7am or later than 10am—and perhaps at certain intervals, that would narrow down the list of numbers we have to replace. It doesn’t sound like that will work, so we’ll just have to replace them all.

With that in mind, here’s the formula (be sure to scroll the box to see the full thing):

SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(Input, 'AM', '')
, 'PM', '')
, ':', '')
, '0', '')
, '1', '')
, '2', '')
, '3', '')
, '4', '')
, '5', '')
, '6', '')
, '7', '')
, '8', '')
, '9', '')
, '  ', ' ')
, '  ', ' ')

[Cole_Wedemeier]

Hi @Mike_Pennisi! I can’t thank you enough for this code that you provided. I can make it run great in a script that I manually run, but when I go to put it in an automation for the exact same table and fields, I get an error for the very last part: output.markdown(**ERROR**: ${error}).

TypeError: Invalid arguments passed to recordQueryResult.getRecord(recordId):
• recordId should be a string, not undefined

The automation I want to setup is that any new record has this script run on it. Could that be the issue in that it’s not trying to do the whole table, and instead, just 1 record within the table?

[Monica_Spaunhor]

Justin_Barrett:

IF({Word Count} > 2, TRIM(MID({Words Expanded}, 20 + FIND({Word 2}, {Words Expanded}) + LEN({Word 2}), 20)))

Freggin Life Saver! Thank you!!!

[Justin_Barrett]

Welcome to the community, @Monica_Spaunhorst1! :grinning_face_with_big_eyes: I appreciate the nod, but that solution is only as good as the formula functions that were available when it was made. Regular expression functions were added more recently and would be a more efficient way to pull this off. I don’t have time to outline the specifics, but the solution that I shared in the thread below could be modified for a use case where the separators are spaces instead of line breaks.

Formula to break a string of text onto 2 separate strings of text Ask the community

Welcome to the community, @Daley_Maasz! :smiley: This is doable, but the three target fields will be formula fields, not single line text fields. Formulas can only dictate their own output; they can’t control the contents of other fields. Here’s the setup in action, followed by the formulas: [Screen Shot 2021-05-19 at 1.51.31 PM] First line… REGEX_EXTRACT({All lines}, "(.*)(?:\n.*)") Second line… REGEX_EXTRACT({All lines}, "(?:.*\n)(.*)(?:\n.*)") Third line… REGEX_EXTRACT({All lines}, "(…

[Ryan_de_Metz]

@Justin_Barrett a follow-up question here if I may.

I have a string of (max) 3 product IDs that get pulled in via Zapier for product IDs as well as the cost for each ID that gets purchased. Up to now, I used a formula to split them as left and right as I only had a max of 2 records. In some cases, only 1 or 2 records will be passed and can be unique each time.

The sample of the data that is sent via Zapier currently looks like

378118,378113 and 597.00,200.00

The formula I am using for splitting the B data set is;

LEFT( RIGHT({Product ID purchased}, LEN({Product ID purchased}) - LEN({Product ID A}) - 1 ), FIND( ",", RIGHT({Product ID purchased}, LEN({Product ID purchased}) - LEN({Product ID A}) - 1 ) & "," )-1 )

I assume I need to use a MID function to get the middle data out but seem to be spinning my heels here. Can you help me out of my misery?

[Justin_Barrett]

Ryan_de_Metz:

I assume I need to use a MID function to get the middle data out but seem to be spinning my heels here. Can you help me out of my misery?

Perhaps. When you get three IDs, is it just something like this:

378118,378113,378121

…or is there more to it?

[Ryan_de_Metz]

Yes, that’s how they go and will only be numbers for that data. The price data will also only be numbers but will include .00 as well.

[Rebecca_Niles]

Hey Mike, 

Would it be possible to split using commas instead of space? 

Thank you!