# Formula to Condense a Series of Numbers with hyphens

Topic Labels: Formulas
Solved
2542 11
cancel
Showing results for
Did you mean:
8 - Airtable Astronomer

I am looking to take a long string of comma separated numbers (it is a string from a look up). Here is an example of numbers to summarize:

1.17-01, 1.17-02, 1.17-03, 1.17-04, 1.17-05, 1.17-06, 1.17-07, 1.17-08, 1.17-09, 1.17-10, 1.17-11, 1.17-12, 1.17-13, 1.17-14, 1.17-17, 1.17-18, 1.33-01, 1.33-02, 1.33-03, 1.33-04, 1.33-05, 1.33-07, 1.33-08, 1.33-09, 1.33-10, 1.33-11, 1.33-12, 1.33-13, 1.33-14, 1.33-15, 1.33-16, 1.33-17

If you look closely, there are missing numbers. I am hoping to find a formula that will output:
1.17-01 --> 1.17-14, 1.17-17, 1.17-18, 1.33-01, --> 1.33-05, 1.33-07, --> 1.33-17

I am flexible on the “–>”, but as you can see a simple dash won’t work, because dashes exist in the numbers. Has anyone seen something like this before? Any help would be greatly appreciated!!

1 Solution

Accepted Solutions
17 - Neptune

Table…

Block…

Outcome…

Code…

``````/*

***********************************************************
Airdrop - Unique Code Batching
Copyright (c) 2020 by Global Technologies Corporation
***********************************************************

*/

output.markdown('# Contiguous Batching');

// get the table name
output.markdown("### Select the Table");
let sourceTable     = await input.tableAsync("Pick the table:");
let sourceTableName = sourceTable.name;

// get the source field name
output.markdown("### Select the Source Field");
let sourceField = await input.fieldAsync("Pick the name of the field to be batched:", sourceTable.id);
let sourceFieldName = sourceField.name;

// get the target field name
output.markdown("### Select the Target Field");
let targetField = await input.fieldAsync("Pick the name field to be UPDATED with the batched values:", sourceTable.id);
let targetFieldName = targetField.name;

// get the records
let result = await sourceTable.selectRecordsAsync();
let sourceRecords = result.records;

// get the record count
let recordCount = sourceRecords.length;

// iterate across all the records
for (var r = 0; r < sourceRecords.length; r++)
{

// get the lst for this record
var thisList = sourceRecords[r].getCellValue(sourceFieldName);

if (thisList != null)
{

let allBatches = "";

// create the array (sans spaces)
let aList = thisList.toString().replace(/ /ig, "").split(",");
aList.sort();
output.inspect(aList);

// create a 2d array
for (var i in aList)
{
}

// batch the sets
let thisBatch = aData[0][0] + " => ";
for (var i = 0; i < aData.length; i++)
{
try {
if (thisNumber + 1 == parseInt(aData[i + 1][1]))
{
// we have continuity
} else {
// continuity broken
allBatches += thisBatch + "\n";
output.markdown(thisBatch);

thisBatch = aData[i + 1][0] + " => ";
// i += 1;
}
} catch (e) {
// do nothing
allBatches += thisBatch + "\n";
output.markdown(thisBatch);
break;
}

}

await sourceTable.updateRecordAsync(sourceRecords[r].id, {
[targetFieldName] : allBatches
});

}
}``````
11 Replies 11
18 - Pluto

I’m still learning Javascript myself, but the only way that you could do this in Airtable would be to write a custom JavaScript script, and then you would need to manually run the JavaScript whenever you wanted to update your result. There are a few JavaScript experts in this community, so hopefully one of them will chime in!

17 - Neptune

I see the missing numbers but I don’t quite follow the pattern you seek to transform this data from or to. What are the rules?

8 - Airtable Astronomer

The data set is in the form of LOT.Location to create a unique ID. The data set is a string of unique IDs. I would like to condense consecutive IDs to be First --> Last, Next, Next, First --> Last, etc…

A simpler version would be condensing 1, 2 ,3 ,4 ,5, 8, 9, 10, 11, 15, 16, 17, 20 to 1 --> 5, 8 --> 11, 15 --> 17, 20.

Let me know if that is still unclear. Thank you!!

17 - Neptune

Yep - I think I see the rules and I’ll be the first to go out on a limb and suggest that without Split() in the formulas toolbox, this problem cannot be solved in a formula. And if someone does by chance nominate a solution, I’ll bet a fine turkey sandwich that it won’t be a practical solution.

The rules seem simple -

1. Sort the unique IDs to create sequential [ascending] order.
2. Batch the contiguous items

Given the sample data, is this the outcome you’re looking at?

If so, here’s a script block that does this - code included below.

``````/*

***********************************************************
Airdrop - Unique Code Batching
Copyright (c) 2020 by Global Technologies Corporation
***********************************************************

*/

output.markdown('# Contiguous Batching');

// create the test list
let thisList = "1.33-10, 1.33-11, 1.33-12, 1.33-13, 1.33-14, 1.17-01, 1.17-02, 1.17-03, 1.17-04, 1.17-05, 1.17-06, 1.17-07, 1.17-08, 1.17-09, 1.17-10, 1.17-11, 1.17-12, 1.17-13, 1.17-14, 1.17-17, 1.17-18, 1.33-01, 1.33-02, 1.33-03, 1.33-04, 1.33-05, 1.33-07, 1.33-08, 1.33-09, 1.33-15, 1.33-16, 1.33-17";

// create the array (sans spaces)
let aList    = thisList.toString().replace(/ /ig, "").split(",");
aList.sort();
output.inspect(aList);

// create a 2d array
for (var i in aList)
{
}

// batch the sets
let thisBatch = aData[0][0] + " => ";
for (var i = 0; i < aData.length; i++)
{
try {
if (thisNumber + 1 == parseInt(aData[i + 1][1]))
{
// we have continuity
} else {
// continuity broken
output.markdown(thisBatch);

thisBatch = aData[i + 1][0] + " => ";
// i += 1;
}
} catch (e) {
// do nothing
output.markdown(thisBatch);
break;
}
}``````
8 - Airtable Astronomer

Bill, this is awesome! I wish I had given more details earlier. Now I need to pull from a field called “Input Components Rollup” and deposit in a new field called “Input Components Condensed”. Would you be willing to help with that? I am not a java script wizard, but I will do my best. Thank you!

17 - Neptune

Good to see we got the requirements nailed down. I’ll try to add that table/field selectivity to the process; slammed today so a little later perhaos.

8 - Airtable Astronomer

Thank you for you willingness to help Bill! I regret not putting all of the details for my application and sincerely appreciate your insight!

17 - Neptune

Table…

Block…

Outcome…

Code…

``````/*

***********************************************************
Airdrop - Unique Code Batching
Copyright (c) 2020 by Global Technologies Corporation
***********************************************************

*/

output.markdown('# Contiguous Batching');

// get the table name
output.markdown("### Select the Table");
let sourceTable     = await input.tableAsync("Pick the table:");
let sourceTableName = sourceTable.name;

// get the source field name
output.markdown("### Select the Source Field");
let sourceField = await input.fieldAsync("Pick the name of the field to be batched:", sourceTable.id);
let sourceFieldName = sourceField.name;

// get the target field name
output.markdown("### Select the Target Field");
let targetField = await input.fieldAsync("Pick the name field to be UPDATED with the batched values:", sourceTable.id);
let targetFieldName = targetField.name;

// get the records
let result = await sourceTable.selectRecordsAsync();
let sourceRecords = result.records;

// get the record count
let recordCount = sourceRecords.length;

// iterate across all the records
for (var r = 0; r < sourceRecords.length; r++)
{

// get the lst for this record
var thisList = sourceRecords[r].getCellValue(sourceFieldName);

if (thisList != null)
{

let allBatches = "";

// create the array (sans spaces)
let aList = thisList.toString().replace(/ /ig, "").split(",");
aList.sort();
output.inspect(aList);

// create a 2d array
for (var i in aList)
{
}

// batch the sets
let thisBatch = aData[0][0] + " => ";
for (var i = 0; i < aData.length; i++)
{
try {
if (thisNumber + 1 == parseInt(aData[i + 1][1]))
{
// we have continuity
} else {
// continuity broken
allBatches += thisBatch + "\n";
output.markdown(thisBatch);

thisBatch = aData[i + 1][0] + " => ";
// i += 1;
}
} catch (e) {
// do nothing
allBatches += thisBatch + "\n";
output.markdown(thisBatch);
break;
}

}

await sourceTable.updateRecordAsync(sourceRecords[r].id, {
[targetFieldName] : allBatches
});

}
}``````
8 - Airtable Astronomer

@Bill.French, this is fantastic work! It accomplishes 99% of what I was looking for. The only major issue is that if there is only one item in a series in the Input Components Rollup field, then it shows 1.17-01 => 1.17-01, which leads to duplicate and confusing information. For now, this still saves me considerable time and effort! I can just go back and edit the text output.

Thank you!

I think this script is an awesome candidate for the nee “Button Field” contest! A few comments I would make to potentially improve this script:

1. Allow a view to be selected instead of a full table.
It is amazing the flexibility you built in to select any table and any two fields! I love it!!! However, it is a pretty slow script. We have about a thousand records in one table and it takes almost a minute to run the script. Being able to select just a single view would reduce the necessary resources considerably.

2. Separate the groups by commas instead of new lines. The primary reason for this script is to condense the information into a smaller space and improve readability. I mostly want it to use in a nice Page designer and the new lines expand the data to take even more room than before with long discontinuous lists.

3. If using the button field, it could either clean up a field in the selected record or a field in all linked records in another table. I would prefer the second option. This would be even better than a view selection!!

I sincerely thank you for your volunteer time on this! I mean no disrespect in my recommendations. The script as is meets all of my most pressing needs. I just can’t help but share the ideas you helped me have! Gracias!!