Hi! So I have fieldA that contains a string of numbers & separators, rolled up from another table (e.g. "10|15|22").
I then have fieldB that contains an integer.
I need to be able to split the string, compare each number to that in fieldB and add the number that's closest (but less than) the number in fieldB into a new fieldC.
Is this possible? Or is there a workaround? I'll take anything, no matter how clunky after going around in circles with this all day!
Here's what pops to mind:
- Add 3 fields to split the data in fieldA using the LEFT, RIGHT, FIND, and MID formulas (or a combination).
- Then add 3 fields to find the difference between each of the above fields and the integer in fieldB.
- You can then create fieldC using a MIN formula to return the lowest number.
If you want to talk about this further or if you ever need any more help, please schedule some time with me. Here's my Calendly link: https://calendly.com/d/zyb-94z-22t/15-minute-help-meeting
You could potentially try looking at Zapier for this. I think they have a text splitting function and a line item function, and there should be an action for comparisons? Not sure about the conditional side of things though