Re: Split text to columns

7992 1
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

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?


19 Replies 19

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)?

4 - Data Explorer
4 - Data Explorer

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.

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.

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(Input, 'AM', '')
, 'PM', '')
, ':', '')
, '0', '')
, '1', '')
, '2', '')
, '3', '')
, '4', '')
, '5', '')
, '6', '')
, '7', '')
, '8', '')
, '9', '')
, '  ', ' ')
, '  ', ' ')

Screen Shot 2020-11-05 at 9.06.03 PM

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?

Freggin Life Saver! Thank you!!!

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.

5 - Automation Enthusiast
5 - Automation Enthusiast

@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?

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


…or is there more to it?

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.

Hey Mike, 

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

Thank you!