Feb 01, 2023 08:23 AM
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!
Feb 01, 2023 11:06 AM
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
Feb 01, 2023 12:32 PM
Thanks Jody, sorry - I should have been clearer - it's not always 3 entries in the string I'm splitting. Technically it could be anything from 0 upwards.
Also, once the string has been split, will its parts be treated as integers?
Feb 01, 2023 12:57 PM
I wondered if this might be possible with a script, but my scripting is extremely basic so that's probably a non-starter regardless
Feb 01, 2023 10:57 PM
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