How to find text in text string

[ARTHUR_BENNETT]

what formula can I run against a text field that will display all text characters up to a “,”.

Example: TEXT1, TEXT2

Result: TEXT1

[Ryan_Bremner]

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?

[W_Vann_Hall]

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.

[Dakota_Bushnell]

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!

[Janet_Shivell]

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?

[Justin_Barrett]

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)

[Janet_Shivell]

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

[EastEndTom]

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.

[Justin_Barrett]

@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?

[wjb]

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}:

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 :point_left:
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.

[Justin_Barrett]

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?