Hi Arthur,

This should work:

LEFT({array field}, SEARCH(",", {array field})-1)

The LEFT function will display all characters up to some index, so you just use the SEARCH function to find the index of the comma, subtracting 1 if you don’t want to include it. It should return blank for a record with no comma.

Hope that helps!

Thank you! Worked like a charm

why does it apply a leading " - - example "Aby

The " appears if you have commas in a lookup field (which you do) or if you have multiple lines in a field.

To adapt the formula to remove the ", use MID instead:

MID({array field}, 1, SEARCH(",", {arrayfield})-2)

Thank you Andy!

How do I reformat this ?

Ashbaugh, Cally

to

Cally Ashbaugh

Assuming ‘Ashbaugh, Cally’ is in a field called {Name}, use this formula:

RIGHT(Name,LEN(Name)-FIND(' ',Name))&' '&LEFT(Name,FIND(',',Name)-1)

Amazing! How do I learn to write these

Start here.

Then go here.

I have been trying variations on this formula for days now and keep getting 0 or #ERROR!. Will this not work when {array field} is a Lookup type field?

If you wrap a lookup-type {Array Field} with ARRAYJOIN(), FIND() will work as expected. You probably should create a scratch formula field with the formula ARRAYJOIN({Array Field}) just to see what the resulting string looks like, as ARRAYJOIN() will alter the Lookup field somewhat. Alternatively, you can use a Rollup field instead of the Lookup.

Looking to do something similar, however I’m just trying to extract 2 people’s first names from a field.

For example:

I need to turn “Doe, John & Jane” into “John & Jane”

Better yet, I need a formula column that will pull (If applicable) First name, Middle Initials/Titles (A. -or- Jr.), Spouse Name, and their Middle Initial/Title as well.

Example 1: “Doe, John A. & Jane B.” to become " John A. & Jane B."

Example 2: “Doe, John A. Jr. & Jane B.” to become “John A. Jr & Jane B.”

Is this even possible?

I already have a separate column that pulls the last name properly.

Any help would be greatly appreciated!

Looking to do something a little different. I have a unique contractor name that includes the contractor name, the SOW 3 digit number (001, 002, etc.) and then SOW name. I am looking to just grab the contractors’ first and last name. Here is an example below.

FirstName LastName 015 SNHU DATA SCI 2

In Excel I would use the following formula: =LEFT(A3,(SEARCH(0,A3,1)-2))

In Airtable A3 = {Contractor Unique}

Although the formula is accepted in Airtable, I get a #ERROR in the field. So this doesn’t quite work in Airtable.

=LEFT({Contractor Unique},(SEARCH(0,{Contractor Unique},1)-2))

Any suggestions for adjusting?

A few things:

1. Airtable formulas don’t begin with an = symbol.


2. To search for the “0” in “015,” you need to wrap the digit in quotes, like I just did here.


3. The final 1 in the SEARCH function isn’t really needed in this case, as searching from position 1 (the first character in the string) is the default. It’s not hurting anything to leave it there, but I’d recommend removing it.


4. The parentheses surrounding the SEARCH function aren’t necessary

What you’re left with (pun partially intended) is this:

LEFT({Contractor Unique}, SEARCH("0",{Contractor Unique})-2)

Thank you for all the tips! This worked perfectly!!! :grinning_face_with_big_eyes:

Hi Airtable-Gurus,

I have a similar problem I’ve been struggling with. I have records with a bunch on info in a single array string, separated by commas. I’d like a formula to

1. 
   

   search a field based on contents (not position, as each record has a different amount of comma separated values in this field)

   
   


2. 
   

   display the result in a unique field without ‘title’ part of the found data.

   
   

For example array contains:

*VFX NO. 101-002-010_E01 ,*FROM CLIP NAME: V001_C044_1234_001.R3D ,*ASC_SOP (1.0000 1.0000 1.0000)(0.0000 0.0000 0.0000)(1.0000 1.0000 1.0000) ,*ASC_SAT 1.0000 ,*SOURCE FILE: V001_C044_1234

And results end up in a formula field called ‘ASC_SAT’ with a value of 1.0000

Any help very much appreciated! Thank you.

@EastEndTom Sorry that this has gone unanswered for so long. Am I reading your comments correctly that you need to extract the value that comes immediately after “ASC_SAT” (in this case “1.0000”), and put that into a similarly-named formula field?

Hey all. I have a similar challenge I’d love some help with. I’m dumping in this data below from Zapier into a field called {Selected Items}:

I’d like to pull out the quantity amounts into their own field. So for example I have created a new field with the name, “CAM” and I’d like that to result in just 1.5 from under the heading CNC Programming

I’m struggling to come up with the right LEFT/RIGHT/SEARCH combination that gets me just the quantity for that lookup.

Where is this data coming from before Zapier gets it? I’m inclined to find a way to separate it earlier in the process, as a formula to isolate the quantities for different fields would be cumbersome at best. It looks like you’ve effectively got an array of dictionaries/objects. Do you want the other items (description, discount, item total, etc.) to also end up in their own fields, or do you only care about the quantity?

Hey. It’s coming from Quotient.

I’m having it dump in all the “Selected items” data but it comes in in an array that’s fairly unhelpful - at least to my coding skillset.

Here’s an example of what Zapier dumps in from Quotient.

Is the {Selected Items} field the only one being updated by Zapier? In the other fields in your screenshot, I see values for quantity, items cost, etc. Are those also from Zapier, or did you pull those out manually?

Every field in this screenshot comes from Quotient to Zapier. The names are very close.

Here is everything that is dumped from Quotient to Zapier in this example:

event_name

quote_accepted

quote_number

18770

title

##-###-R## Job Name

quote_url

https://go.quotientapp.com/q/UNIQUE-STRING

from

My Company

for

Their Company 

first_sent

2020-10-09T01:54:23+00:00

valid_until

2020-11-08T01:50:00+00:00

quote_status

Accepted

is_archived

false

currency

USD

amounts_are

No Tax

overall_discount

0

quote_for

name_first

Customer

name_last

Surname

email

customer@email.com

company_name

Their Company

phone

type

Primary Phone

value

(555) 555-5555

address

type

street

city

state

zip

country

item_headings

Shop Supplies & Tool Usage

CNC Programming

CNC Machining

total_includes_tax

1728.16

total_excludes_tax

1728.16

discount_amount_includes_tax

0

discount_amount_excludes_tax

0

selected_items

0

item_code

heading

Shop Supplies & Tool Usage

description

sales_category

Materials & Supplies

tax_rate

0

tax_description

No tax

subscription

discount

0

cost_price

38

unit_price

48.16

quantity

1

item_total

48.16

1

item_code

CAM/CAD

heading

CNC Programming

description

sales_category

CAM/CAD Design

tax_rate

0

tax_description

No tax

subscription

discount

0

cost_price

60

unit_price

120

quantity

1.5

item_total

180

2

item_code

CNC Machining

heading

CNC Machining

description

sales_category

CNC Routing

tax_rate

0

tax_description

No tax

subscription

discount

0

cost_price

60

unit_price

120

quantity

12.5

item_total

1500

accepted

accepted_on_behalf

false

accepted_on_behalf_who

order_number

comments

when

2020-10-09T02:12:26+00:00

by

name_first

Customer

name_last

Surname

email

customer@email.com

company_name

Their Company

phone

type

Primary Phone

value

(555) 555-5555

address

type

street

city

state

zip

country

event_name

quote_accepted

quote_number

18770

title

##-###-R## Job Name

quote_url

https://go.quotientapp.com/q/UNIQUE-STRING

from

My Company

for

Their Company 

first_sent

2020-10-09T01:54:23+00:00

valid_until

2020-11-08T01:50:00+00:00

quote_status

Accepted

is_archived

false

currency

USD

amounts_are

No Tax

overall_discount

0

quote_for

name_first

Customer

name_last

Surname

email

customer@email.com

company_name

Their Company

phone

type

Primary Phone

value

(555) 555-5555

address

type

street

city

state

zip

country

item_headings

Shop Supplies & Tool Usage

CNC Programming

CNC Machining

total_includes_tax

1728.16

total_excludes_tax

1728.16

discount_amount_includes_tax

0

discount_amount_excludes_tax

0

selected_items

0

item_code

heading

Shop Supplies & Tool Usage

description

sales_category

Materials & Supplies

tax_rate

0

tax_description

No tax

subscription

discount

0

cost_price

38

unit_price

48.16

quantity

1

item_total

48.16

1

item_code

CAM/CAD

heading

CNC Programming

description

sales_category

CAM/CAD Design

tax_rate

0

tax_description

No tax

subscription

discount

0

cost_price

60

unit_price

120

quantity

1.5

item_total

180

2

item_code

CNC Machining

heading

CNC Machining

description

sales_category

CNC Routing

tax_rate

0

tax_description

No tax

subscription

discount

0

cost_price

60

unit_price

120

quantity

12.5

item_total

1500

accepted

accepted_on_behalf

false

accepted_on_behalf_who

order_number

comments

when

2020-10-09T02:12:26+00:00

by

name_first

Customer

name_last

Surname

email

customer@email.com

company_name

Their Company

phone

type

Primary Phone

value

(555) 555-5555

address

type

street

city

state

zip

country

Thanks for the clarification. It looks like the values you want are already there. The {Selected Items} field shows all of the relevant details for each item, while the other fields to the right break down specific pieces of those items. In your initial message you said that you wanted the quantity. There were three item lists, with the respective quantities being 1, 1.5, and 12.5. If you look in the {Selected Items Qty} field in your screenshots, you’ll see:

1, 1.5, 12.5

Do you want to extract one specific quantity (i.e. just the second item), or do something with all of the quantities as a group? If it’s the latter, that will require a script because they’ve been transformed into a single string, and Airtable formulas can’t (yet) parse strings with that much precision. However, if it’s just a matter of extracting a specific number, that can be done if you could share more info on how to identify the number you want.

Hi Cameron. How would I find TEXT 2 in Arthur’s example? Or in other words, how would I find everything to the right of the comma?

As long as there’s only a single comma in the source, this will work:

IF(Source, REGEX_EXTRACT(Source, "(?:,\\s?)(.*)"))

…or this:

IF(Source, TRIM(MID(Source, FIND(",", Source) + 1, 50)))

The first version is more efficient because you don’t have to specify how many characters you want to extract (the 50 value from the second version, which will need to be increased for longer strings).