Skip to main content

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

Jody

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?

I wondered if this might be possible with a script, but my scripting is extremely basic so that's probably a non-starter regardless

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

Using a script for this would definitely work and is fairly simple to do; if you know someone who knows a bit of JavaScript they'll probably be able to whip this up for you in an hour tops

Reply


Terms & Conditions