Save the date! Join us on October 16 for our Product Ops launch event. Register here.
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
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:
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.
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?
Sep 20, 2019 06:23 AM
Can someone please help me?
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)) )
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.
Sep 29, 2019 08:38 AM
Thanks for everything
Where will I enter this API?
Sep 29, 2019 09:47 AM
To do this via the API you need the following:
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().
Sep 29, 2019 10:16 AM
OK I understand you now & again thanks for all your help till now, I am using heavily the formula you created for me
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.]*$/"
)