How to count the number of item separators in a list to return a value in between

I’m using OCR to scan a bill and have a separate column that extracts prices and that returns about 60 values. I set the item separator to “/” and want to get the value between the 5th and 6th separator.
Does anyone know what formula could be used to achieve this?

$87,032.00 /$2,019.00 /$1.00 /$205.00 /$16.00 /$214.11 /$33,704.00 /$25.00 /$206.00 /$33,704.00 /$705,049,118.00 /$3,340,481.00 /$31,744.00 /$30,342.00 /$1,402.00 /$1,402.00 /295.69 /-1,001.00 /$0.07 /$8.00 /

1 Like

To return $214.11, try this:

INDEX(EXTRACTPRICES("$87,032.00 /$2,019.00 /$1.00 /$205.00 /$16.00 /$214.11 /$33,704.00 /$25.00 /$206.00 /$33,704.00 /$705,049,118.00 /$3,340,481.00 /$31,744.00 /$30,342.00 /$1,402.00 /$1,402.00 /295.69 /-1,001.00 /$0.07 /$8.00 /"), 6)

Here’s links to explanations of these functions in the docs:


3 Likes

Perfect, thanks!