Extract a number from a long Cell

[Abraham_Bochner]

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

[Bill_French]

Abraham_Bochner:

23.93

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:

1. There are two values to the left of the number being extracted and both are delimited by a semi-colon (in every case).
2. 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.

[Abraham_Bochner]

I got back the following error:

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

Did you created another column?

[Abraham_Bochner]

Can someone please help me?

[Bill_French]

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

[Bill_French]

@Abraham_Bochner,

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.

[Abraham_Bochner]

Thanks for everything

Where will I enter this API?

[Bill_French]

Abraham_Bochner:

Where will I enter this API?

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

[Abraham_Bochner]

OK I understand you now & again thanks for all your help till now, I am using heavily the formula you created for me

[kuovonne]

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.]*$/"
)