Get first item from Split()

I use the SPLIT function to split a string which looks something like 1.23/1.15.

Right now it yields two cells, as it should. But how do I get a certain element from the result? I would like to do something like this:

SPLIT("1.23/1.15", "/")[0]

to extract 1.23. What is the correct syntax for that?

I tried using the INDEX function, without success: =INDEX(SPLIT("1.23/1.15", "/"), 0,0)

Solution 1:

You can use the index function to select which value to return. So to retrieve the second value from your example you could use:

=index(SPLIT("1.23/1.15", "/"), 0, 2)

The last argument says which column you wish to retrieve - 1 would retrieve the first value.

Alternatively you could use left / right and find to extract either value from your example. For example to get the first value you could use:

=left("1.23/1.15", find("/", "1.23/1.15"))

Solution 2:

The problem with the above two solutions is they are not supported inside an arrayformula function. If you wrap in a query function, you get the desired result and is very flexible in terms of parsing just the field you are looking to return:

Return 1st Column

=query(SPLIT("1.23/1.15", "/"), "SELECT Col1")

Return 2nd Column

=query(SPLIT("1.23/1.15", "/"), "SELECT Col2")