Formula to break a string of text onto 2 separate strings of text

[J_B_S]

I have a string of text that I want to break into two separate strings of text.
Here’s an example:

• This is an example string of text / I want to break it into two separate strings of text

I want all of the words that precede the “/” to be one string of text. And then I want all of the words that come after the “/” to be a separate string of text. I want to completely eliminate the “/”.

So, to recap, we start with this:

• This is an example string of text / I want to break it into two separate parts

And then we end-up with this:

• This is an example string of text
• I want to break it into two separate parts

[Justin_Barrett]

First off, there’s no need to wrap the DATETIME_FORMAT() function in parentheses to separate it from later pieces that you’re adding. They can all be added sequentially like this:

DATETIME_FORMAT({Date of donation}, 'M/D/YYYY') & "—" & Donor

Extra parentheses groups are really only necessary if some part of the formula needs to be calculated separately; e.g. to control the order of operations with a numerical calculation.

To your question, I would wrap the IF() condition check just around the tail end of that formula, like this:

DATETIME_FORMAT({Date of donation}, 'M/D/YYYY') & "—" & IF({On behalf of corporation}, {Donor Employer}, Donor)

[Angelena_Zeiser]

Thank you very much for teaching me! I appreciate it!

[Christie_Kittle]

Thanks for all the help! I am seeing some great solutions, but they seem to be jumping around with type of formula. How do you decide which formula to use?

We want to separate name: Last, First into two columns - first and last
We also want to separate terms for higher ed. From “Spring 2022” into two columns - Spring and 2022.

[kuovonne]

Welcome to the Airtable community!

There are many ways to do things in code, and complex formulas are code. When deciding which formula to use, it is often a matter of taste/style. Use whichever formula gives the result you want and makes sense to you. The better you understand a formula, the easier it will be for you to maintain it in case you ever need changes. And it is okay to change formulas as you needs and knowledge grow.

[Gianfranco_Gall]

Hello! I see that you are very helpful in this forum!

I am trying to solve something in reverse of all of the requests here.

I have dimensions for packages, stored in: {Length}, {Width}, {Height}, {Weight}.

I would like a formula to concatenate them with TABs in between them, like this: {Length} TAB {Width} TAB {Height} TAB TAB TAB {Weight}.

I first tried to string concatenate it but TAB es read just like a SPACE. Then I tried to create an array, but it seems that the formula field doesn’t accept arrays.

The use that I want to give this is one field that could be turned into a barcode or just copied and pasted to a warehouse management system.

Thanks in advance!

[paul_evans]

Hi All

I’ve scoured this thread and I am still struggling.

I have a spreadsheet which lists. analytics tools different websites.

In a single row, I have a record like:

Google Analytics ; Facebook Pixel (2.9.83) ; Hotjar ; Google Ads Conversion Tracking ; Cloudflare Browser Insights

I’d like each tool to be a separate entry in a single field. I am thinking a ‘multiple select’ field type.

Any advice would be greatly appreciated.

Thanks
Paul

[Justin_Barrett]

Gianfranco_Gallese:

I first tried to string concatenate it but TAB es read just like a SPACE.

Correct. First off, all whitespace—including spaces and Tab characters—is condensed when displayed in a web browser (unless you insert a non-breaking space via HTML, but that’s another story). Another issue is that the Tab character in a browser doesn’t inherently represent a specific gap size, unlike the Tab behavior on a typewriter (where the concept came from) where the carriage would advance to predefined tab stops. Without some definition of how far to advance when inserting a Tab, the browser just treats it like a single space.

Unfortunately there really isn’t a good way to do the kind of formatting that you’re looking for inside a field. While extra spaces/Tabs will be stored internally, they’re all mashed together for display unless you’re looking at the expanded view of a single field (press SHIFT + SPACE with any single field selected to expand just that field) where Airtable actually makes the effort to ensure that the field data displays precisely as stored.

In short, Airtable fields are great for storing data, but not necessarily the best for designing spacing-dependent layouts for that data.

Gianfranco_Gallese:

Then I tried to create an array, but it seems that the formula field doesn’t accept arrays.

Correct again. While Airtable’s formula system does contain functions for processing arrays, those only work with data from lookup and rollup fields, the only field types that produce arrays. There’s no way currently to create an array from scratch. Even if there were, an array wouldn’t really help in this instance.

Gianfranco_Gallese:

The use that I want to give this is one field that could be turned into a barcode or just copied and pasted to a warehouse management system.

I’m not seeing how those four values would translate into a barcode of any kind by just laying them out with specific space between them. I’m also not sure how that spacing would help when copying the data elsewhere. Could you go into more detail about your desired use case?

[Gianfranco_Gall]

Hello @Justin_Barrett , thank you very much for your response.

A little bit of background here: I want warehouse personnel to be able to register entering cargo to the warehouse from their phones with the airtable app.

At the moment, we use a WMS software that looks like this:

This software (Magaya) doesn’t integrate with many other softwares. We use a dimensioner (a scale with depth cameras to calculate the volume and weight of the package). This dimensioner hasn’t got an interface with Magaya, because Magaya has its own; so they created a barcode that includes TABs, so that when it is scanned, the dimensions and weight are filled in Magaya automatically.

We are exploring alternatives to Magaya because of its lack of integration possibilities and adaptation to new ideas. What I was thinking was using Airtable to replace this initial process, however we still need to fill the info in Magaya at the moment. To reduce the extra steps in this process, I was thinking about using the same procedure that the dimensioner uses: the Barcode (or even a copy/paste)

[Justin_Barrett]

Thanks for the clarification. That’s definitely a unique use case you’re working with!

Without knowing the inner workings of the barcode system, I don’t think that it will be possible to replicate that. However, it should be possible to pull this off using Typinator, which is a really handy tool that lets you define your own shortcodes that auto-expand into longer chunks of text, including things like tabs and other control characters. Here’s how I’m envisioning its use in this case:

• You use a formula to concatenate the values that you want to insert into those fields, separating them with a single space between them
• In Typinator, define an expansion that uses JavaScript to extract the pieces from the copied text, then output them individually with the proper number of tabs between them.

To use this, the user would copy the formula output from an Airtable field, click in the first field (length) in the Magaya dialog, then enter the defined shortcode. This would trigger Typinator to parse the copied text and insert the values into the proper fields.

I ran a quick test on this and it worked well, though it was a little tricky to set up. If you’d like help with this, message me directly and we can discuss the options.

[Gianfranco_Gall]

Thank you very much @Justin_Barrett!