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

[Unai_Ezkurra]

Hi Justin,

Thanks for your quick replay.

• No, for example this could be the largest hypothetical example: FICHAR (7), INTERESANTE (11), SEGUIR EVOLUCION (8), SEGUIR JOVEN (3), FIDELIZAR 2A (4), FIDELIZAR 2B (13), FIDELIZAR 3 (8), DESCARTAR (1), –SIN VALORACION– (1).

• No. could be 9 & 11 for example.

[Justin_Barrett]

Where are the numbers coming from? Are they being added to that string via a formula elsewhere in the base? I’m just wondering if it’s possible to extract the numbers before they get put into the massive string.

Also, what type of field is this string in? Is it just a single line text, or maybe a lookup/rollup field?

[Unai_Ezkurra]

They are imported from another data base. The string is not a formula it is a single text. That info is already send it from the provider like this.

[Justin_Barrett]

This turned out to be a lot trickier than I initially expected, but I got something that works.

I first added a field that spreads out each item using large blocks of spaces. In my test, I found that a fairly high number was required, so I went with 50. I also removed the parentheses, as those were messing up the final calculation. I put the raw text in a field named {Source}, but obviously you can rename this as needed for your base.

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Source, ",", REPT(" ", 50)), "(", ""), ")", "")

I named this formula field {Spread}. Using your longest example, it gave me this (looking at the expanded view of the field:

I then extracted each section in a separate formula field. The field names were {Item 1}, {Item 2}, {Item 3}, etc. The formulas are similar, but each one depends on what was extracted by the one before it. This technique takes advantage of the fact that the MID() function doesn’t care if you tell it to extract part of a string that doesn’t exist (e.g. start at character 250 of a string that’s only 100 characters long).

Item 1

TRIM(MID(Spread, 1, 50))

Item 2

TRIM(MID(Spread, 50 + LEN({Item 1}), 50))

Item 3

TRIM(MID(Spread, 100 + LEN({Item 1} & {Item 2}), 50))

Item 4

TRIM(MID(Spread, 150 + LEN({Item 1} & {Item 2} & {Item 3}), 50))

Item 5

TRIM(MID(Spread, 200 + LEN({Item 1} & {Item 2} & {Item 3} & {Item 4}), 50))

Item 6

TRIM(MID(Spread, 250 + LEN({Item 1} & {Item 2} & {Item 3} & {Item 4} & {Item 5}), 50))

Item 7

TRIM(MID(Spread, 300 + LEN({Item 1} & {Item 2} & {Item 3} & {Item 4} & {Item 5} & {Item 6}), 50))

Item 8

TRIM(MID(Spread, 350 + LEN({Item 1} & {Item 2} & {Item 3} & {Item 4} & {Item 5} & {Item 6} & {Item 7}), 50))

Item 9

TRIM(MID(Spread, 400 + LEN({Item 1} & {Item 2} & {Item 3} & {Item 4} & {Item 5} & {Item 6} & {Item 7} & {Item 8}), 50))

Finally, there’s the {Total} formula field that adds up the respective numbers from each section:

SUM(
    IF({Item 1}, VALUE(RIGHT({Item 1}, 2))),
    IF({Item 2}, VALUE(RIGHT({Item 2}, 2))),
    IF({Item 3}, VALUE(RIGHT({Item 3}, 2))),
    IF({Item 4}, VALUE(RIGHT({Item 4}, 2))),
    IF({Item 5}, VALUE(RIGHT({Item 5}, 2))),
    IF({Item 6}, VALUE(RIGHT({Item 6}, 2))),
    IF({Item 7}, VALUE(RIGHT({Item 7}, 2))),
    IF({Item 8}, VALUE(RIGHT({Item 8}, 2))),
    IF({Item 9}, VALUE(RIGHT({Item 9}, 2)))
)

Here’s the output from my test:

[Unai_Ezkurra]

SUPERB Justin!!!

It works perfectly!!! Many thanks!

[Stive_Fernandes]

Hello guys!

i am having a bit of trouble starting my project on airtable so i hope you can help me…
I am trying to webscrap different data from companies t the issue i have is that i wanted a table base with the companies but also a linked one with all representants of that company listed in that table.
The problem i have is that i get a list of every representants name, date of birth etc, that is filled in a single field.

Usually i enter a SIREN code that gets my Zap webhook to extract the informations i want, with all the representants of the company, but those are most of the time several. For exemple for one company i can have 5 representants, but i am having trouble to get airtable to create 5 different lines for each one so i can webscrap in that table more information about them (date of birth for exemple…).

I’m sorry for my english i am from France and probably really bad at that, i hope that you can help me in some way!

[Justin_Barrett]

Welcome to the community, @Stive_Fernandes_Alve! :grinning_face_with_big_eyes: First off, your question is only marginally related to the original post that started this thread (as are some others that have kept this thread alive since it was kicked off in May 2019). Unless the issue you’re encountering is virtually identical to one you’ve found in another thread, it’s better to start a new thread than add to an existing one.

That aside, this is technically doable with Airtable formulas, but the required data extraction is so complex that building such a system wouldn’t be a major effort (and frankly not one that I’d enjoy tackling, even though I love a good challenge). It would be much easier to process this data and create the relevant records by parsing it with a script. Zapier does have a scripting module that you can insert into a zap, but my Zapier knowledge is cursory at best, so I’m not certain if this could be set up later to add multiple records from the extracted data.

My inclination would be to send the full combined data to Airtable as a record in a table that’s specifically made to process this stuff. That new record could trigger an automation that runs a script to parse it and make the necessary records in your staff table. Just be aware that this can only be done if your base is in a Pro-plan workspace or higher.

[Steffen_Giebele]

Hi there,

this is my first post, so please be kind! :slightly_smiling_face:

I used the original formula and solution to create a Firstname Field and Lastname Field from a Fullname field.

All is well as long as the Fullname field is just 2 words, like Jane Doe. As soon as the Fullname field has e.g. Jane Mary Doe I get Firstname = Jane and Lastname = Mary Doe.

Essentially I am looking for a workaround for the SPLIT() function in Google Sheets.

Thanks for your help!

[Justin_Barrett]

Welcome to the community, @Steffen_Giebeler! :grinning_face_with_big_eyes: Here’s a version that uses a combination of old and new techniques.

For the {First Name} field, I’m using a variation of the original formula I posted a couple of years ago:

IF({Full Name}, LEFT({Full Name}, FIND(" ", {Full Name}) - 1))

For {Last Name}, I’m using a regular expression to find all names except the last one and blank them out, leaving only the last name (provided that the last name doesn’t have a trailing space after it):

IF({Full Name}, REGEX_REPLACE({Full Name}, ".+\\s", ""))

[Justin_Barrett]

As an update, here’s another way to extract the first name using a regular expression (thanks to @ScottWorld for helping me troubleshoot the syntax; this stuff is so finicky!):

IF({Full Name}, TRIM(REGEX_EXTRACT({Full Name}, ".*?\\s")))