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

[wjb]

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.

[Justin_Barrett]

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?

[wjb]

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

[Justin_Barrett]

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.

[Kate_Price]

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?

[Justin_Barrett]

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).

[Kate_Price]

Both worked perfectly. Thank you so much!!

[Frank_Reagan]

How do I extract the third item in an array?
Example: One, Two, Third Item, Four, Five
Result: Third Item

[Justin_Barrett]

@Frank_Reagan If it’s a literal array, you’ll first need to convert it to a string. Array indexing isn’t currently possible using formulas. The easiest way to convert an array to a string is with the ARRAYJOIN() function:

ARRAYJOIN(array, ",")

Assuming you’ve got a string, there are at least three items in that string, and there are commas separating each item (as in your example), then this would work:

IF(String, REGEX_EXTRACT(String, "(?:[^,]*, *){2}([^,]*)(?:.*)"))

Your sample also has a space after each comma; this formula will also take that into account in case your data may include that.

[Frank_Reagan]

This is incredibly helpful, Justin!

Can you explain this aspect of the formula?

"(?:[^,]*, *){2}([^,]*)(?:.*)"

I’d like to understand this more so that I can adapt it to find additional item numbers in a string list, not just the 3rd position. Would I get the fourth item with this modification:
"(?:[^,]*, *){3}([^,]*)(?:.*)"

For your entertainment, I have a working solution to find up to the 10th position within a string list (using multiple fields in order to increment and iterate through the list), but it is overly complex and requires too many helper fields in order to achieve:

• {Items_count} = count field of {Linked_records} field
• {Original_string} = convert {Linked_records} field into csv string
• {First_item} = LEFT({Original_string},FIND(", “,{Original_string})-1);
• {Remaining_items_1} = IF({Items_count}>1,MID({Original_string},LEN(LEFT({Original_string},FIND(”, “,{Original_string})+2)),LEN({Original_string})),”");
• {Second_item} = IF({Items_count}=2,{Remaining_items_1},LEFT({Remaining_items_1},FIND(", “,{Remaining_items_1})-1));
• {Remaining_items_2} = IF({Items_count}>2,MID({Remaining_items_1},LEN(LEFT({{Remaining_items_1},FIND(”, “,{Remaining_items_1})+2)),LEN({{Remaining_items_1})),”");
• {Third_item} = IF({Items_count}=3,{Remaining_items_2},LEFT({Remaining_items_2},FIND(", ",{Remaining_items_2})-1));
• Etc…
:grinning_face_with_sweat: