Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Extract a number from a long Cell

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Topic Labels:
Formulas

1
6577
9

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Sep 19, 2019 03:12 PM

This is the data in my cell

<ANUZ2B2KI2XTG;ANUZ2B2KI2XTG;23.93+0=23.93;Me;26.12;Ig:r#5:><A20AY8YZ1X41HY;A20AY8YZ1X41HY;23.96+0=23.96;Me;26.12;Ig:r#5:><A2FATTUNY7ADJL;A2FATTUNY7ADJL;23.99+0=23.99;Me;26.12;Ig:r#5:>

I am trying to extract the first number which is “23.93” from the cell

Reply

9 Replies 9

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Sep 19, 2019 04:16 PM

Like this?

This formula does it, but I tested it only one the one string you provided and two others (which I randomly generated). Feel free to reformat it and add comments so even I can understand it. :winking_face:

`RIGHT( LEFT(MID({Data Cell}, FIND(";", {Data Cell}) + 1, FIND("+", {Data Cell}) - 1), FIND("+", {Split(1)}) - 1), LEN(LEFT(MID({Data Cell}, FIND(";", {Data Cell}) + 1, FIND("+", {Data Cell}) - 1), FIND("+", MID({Data Cell}, FIND(";", {Data Cell}) + 1, FIND("+", {Data Cell}) - 1)) - 1)) - FIND(";", LEFT(MID({Data Cell}, FIND(";", {Data Cell}) + 1, FIND("+", {Data Cell}) - 1), FIND("+", MID({Data Cell}, FIND(";", {Data Cell}) + 1, FIND("+", {Data Cell}) - 1)) - 1)) )`

WARNING - this formula makes the following assumptions about your data pattern:

- There are two values to the left of the number being extracted and both are delimited by a semi-colon (in every case).
- There is a “+” sign immediately following the number being extracted (in every case).

This is essentially a parser for two values to the left (i.e., second semi-colon) and the first plus sign. I’ll bet it can be simplified.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Sep 19, 2019 04:31 PM

I got back the following error:

“Sorry, there was a problem saving this field. Unknown field names: split(1)”

Did you created another column?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Sep 20, 2019 06:23 AM

Can someone please help me?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Sep 20, 2019 12:52 PM

Sorry @Abraham_Bochner, I think there was one missed transformation in my example (Split(0)).

Here’s the corrected formula…

`RIGHT( LEFT(MID({Data Cell}, FIND(";", {Data Cell}) + 1, FIND("+", {Data Cell}) - 1), FIND("+", MID({Data Cell}, FIND(";", {Data Cell}) + 1, FIND("+", {Data Cell}) - 1)) - 1), LEN(LEFT(MID({Data Cell}, FIND(";", {Data Cell}) + 1, FIND("+", {Data Cell}) - 1), FIND("+", MID({Data Cell}, FIND(";", {Data Cell}) + 1, FIND("+", {Data Cell}) - 1)) - 1)) - FIND(";", LEFT(MID({Data Cell}, FIND(";", {Data Cell}) + 1, FIND("+", {Data Cell}) - 1), FIND("+", MID({Data Cell}, FIND(";", {Data Cell}) + 1, FIND("+", {Data Cell}) - 1)) - 1)) )`

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Sep 29, 2019 08:23 AM

I was creating a number of examples to test my API framework for field transformations and I decided to use your formula question as a good test.

This simple script (processed using the API) …

```
// set the base to Airdrop Demo
set base to appAvzbF1dJ9OkgMn
// set the table to the string parsing demo
set table to String Parsing Demo
// update the values from the codes
replace all {{Values}} with {{Codes}}.parseValues()
// parse values
function parseValues(code) {
var thisValue = code.split(";")[2].split("+")[0];
return(parseFloat(thisValue));
}
```

Replaces this formula…

`RIGHT( LEFT(MID({Data Cell}, FIND(";", {Data Cell}) + 1, FIND("+", {Data Cell}) - 1), FIND("+", MID({Data Cell}, FIND(";", {Data Cell}) + 1, FIND("+", {Data Cell}) - 1)) - 1), LEN(LEFT(MID({Data Cell}, FIND(";", {Data Cell}) + 1, FIND("+", {Data Cell}) - 1), FIND("+", MID({Data Cell}, FIND(";", {Data Cell}) + 1, FIND("+", {Data Cell}) - 1)) - 1)) - FIND(";", LEFT(MID({Data Cell}, FIND(";", {Data Cell}) + 1, FIND("+", {Data Cell}) - 1), FIND("+", MID({Data Cell}, FIND(";", {Data Cell}) + 1, FIND("+", {Data Cell}) - 1)) - 1)) )`

More evidence that a Split() method would vastly simplify parsing processes.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Sep 29, 2019 08:38 AM

Thanks for everything

Where will I enter this API?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Sep 29, 2019 09:47 AM

To do this via the API you need the following:

- Javascript programming skills.
- A server (or access to a serverless) programming environment such as Zapier, Google Apps Script, or perhaps Heroku.
- A working knowledge of APIs in general, and specifically the Airtable API.

That’s a heavy lift for anyone and why I believe the Split() function must be made available in Airtable to end this insanity.

My Airdrop script shown earlier is a tool I created for my own consulting projects to make it fast and easy to perform any type of field transformations without using formulas. This is especially useful in solutions where I want to avoid complex formulas and also where formulas have to be maintained in many locations.

For your parsing objective, Airdrop (or the API) is like using a wrecking ball to turn off a light switch. But your example is the perfect way to demonstrate why Airtable must support Split().

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Sep 29, 2019 10:16 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Feb 27, 2020 09:04 AM

Here is a situation where it would be nice to have a `REG_EX_SEARCH`

function that uses regular expression pattern matching, as suggested here.

```
REG_EX_SEARCH({field name}, "/<[\w]*;[\w]*;[\d.]*/m")
```

Followed by

```
REG_EX_SEARCH({first part}, "/[\d.]*$/")
```

The two functions embedded together:

```
REG_EX_SEARCH(
REG_EX_SEARCH({field name}, "/<[\w]*;[\w]*;[\d.]*/m"),
"/[\d.]*$/"
)
```

Reply