Help

Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

Split text with linear range to multiple columns

Topic Labels: Formulas
Solved
Jump to Solution
438 2
cancel
Showing results for 
Search instead for 
Did you mean: 

Hello community!

I'm trying to extract some words of a column in 2 new columns. The number of words is always the same before the parentheses, example :

2 big cars (With little windows)
3 big cars (With black roof)
4 big cars (With a lot of gas)

I'm trying to get on column 1 :

2 big cars
3 big cars
4 big cars

And on column 2, only the information in parentheses :

With little windows
With black roof
With a lot of gas

Can you please help me ? 🙂

Cheers

1 Solution

Accepted Solutions

Hi @alternaz!🙂

If I understand correctly, you want to extract words from at the beginning of your text and extract what is in the parenthesis in another column. Here is a way to have the result you want.

Because the length of the text inside the parenthesis varies, we need to tackle this in several steps.

Assuming the field where all the text is written is called {Name}.

1- To extract the first part in a new column, use this in a new formula field called {1st part}:

LEFT(Name,10)

Output : 2 big cars

2- To extract the part with parenthesis:
2a-Make a new formula field that will extract the parenthesis with the text, name this field {2nd part}:

MID(Name,12,21)

Output : (With little windows)
*You can increase this number (21) if what you expect longer text between parenthesis.

2b-Remove the first parenthesis from the field {2nd part}, for this create a new field {1st parenthesis} with this formula :

SUBSTITUTE({2nd part},'(',' ')

Output : With little windows)

We substituted the 1st parenthesis with a space.

2c-Remove the second parenthesis from the field {1st parenthesis}, for this, create a new field {2nd parenthesis} with this formula :

SUBSTITUTE({1st parenthesis},')',' ')

Output: With little windows

We substituted the 2nd parenthesis with a space.

3- Hide the two fields that were used for calculation only. That way, you will only have displayed the fields that are interesting for you.

I attached the screenshots of the steps I mentioned to illustrate them.

You can explore and play around the formulas I used in the Airtable "Formula Field Reference" at this address : https://support.airtable.com/docs/formula-field-reference

I truly hope this can be of help, so you can build your car base ! 🚗🌞

Cheers

See Solution in Thread

2 Replies 2

Hi @alternaz!🙂

If I understand correctly, you want to extract words from at the beginning of your text and extract what is in the parenthesis in another column. Here is a way to have the result you want.

Because the length of the text inside the parenthesis varies, we need to tackle this in several steps.

Assuming the field where all the text is written is called {Name}.

1- To extract the first part in a new column, use this in a new formula field called {1st part}:

LEFT(Name,10)

Output : 2 big cars

2- To extract the part with parenthesis:
2a-Make a new formula field that will extract the parenthesis with the text, name this field {2nd part}:

MID(Name,12,21)

Output : (With little windows)
*You can increase this number (21) if what you expect longer text between parenthesis.

2b-Remove the first parenthesis from the field {2nd part}, for this create a new field {1st parenthesis} with this formula :

SUBSTITUTE({2nd part},'(',' ')

Output : With little windows)

We substituted the 1st parenthesis with a space.

2c-Remove the second parenthesis from the field {1st parenthesis}, for this, create a new field {2nd parenthesis} with this formula :

SUBSTITUTE({1st parenthesis},')',' ')

Output: With little windows

We substituted the 2nd parenthesis with a space.

3- Hide the two fields that were used for calculation only. That way, you will only have displayed the fields that are interesting for you.

I attached the screenshots of the steps I mentioned to illustrate them.

You can explore and play around the formulas I used in the Airtable "Formula Field Reference" at this address : https://support.airtable.com/docs/formula-field-reference

I truly hope this can be of help, so you can build your car base ! 🚗🌞

Cheers

Thanks a lot for this perfect answer !!