what formula can I run against a text field that will display all text characters up to a “,”.
Example: TEXT1, TEXT2
Result: TEXT1
what formula can I run against a text field that will display all text characters up to a “,”.
Example: TEXT1, TEXT2
Result: TEXT1
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
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)
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
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
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?
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.
Assuming ‘Ashbaugh, Cally’ is in a field called {Name}
, use this formula:
RIGHT(Name,LEN(Name)-FIND(' ',Name))&' '&LEFT(Name,FIND(',',Name)-1)
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!
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!
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?
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:
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
search a field based on contents (not position, as each record has a different amount of comma separated values in this field)
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.
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
search a field based on contents (not position, as each record has a different amount of comma separated values in this field)
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}:
description:
discount: 0
heading: Shop Supplies & Tool Usage
item_code:
item_total: 49.16
quantity: 1
sales_category: Materials & Supplies
subscription:
tax_description: No tax
tax_rate: 0
unit_price: 49.16
description:
discount: 0
heading: CNC Programming
item_code: CAM/CAD
item_total: 210
quantity: 1.5
sales_category: CAM/CAD Design
subscription:
tax_description: No tax
tax_rate: 0
unit_price: 140
description:
discount: 0
heading: CNC Machining
item_code: CNC Machining - note
item_total: 1750
quantity: 12.5
sales_category: CNC Routing
subscription:
tax_description: No tax
tax_rate: 0
unit_price: 140
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.
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}:
description:
discount: 0
heading: Shop Supplies & Tool Usage
item_code:
item_total: 49.16
quantity: 1
sales_category: Materials & Supplies
subscription:
tax_description: No tax
tax_rate: 0
unit_price: 49.16
description:
discount: 0
heading: CNC Programming
item_code: CAM/CAD
item_total: 210
quantity: 1.5
sales_category: CAM/CAD Design
subscription:
tax_description: No tax
tax_rate: 0
unit_price: 140
description:
discount: 0
heading: CNC Machining
item_code: CNC Machining - note
item_total: 1750
quantity: 12.5
sales_category: CNC Routing
subscription:
tax_description: No tax
tax_rate: 0
unit_price: 140
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?
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.
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?
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
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
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 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!
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?
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).
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.