This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.

Turn on suggestions

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

Showing results for

- 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
4843
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