Get first item from Split()

Question

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)

gar · Accepted Answer · 2019-02-18T17:29:25.883

283

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"))

edited Feb 18 '19 at 17:29

answered Aug 31 '15 at 09:28

gar

14,152
5
30
31

7

Oh, I just presumed index was zero-based. Thanks – filur Aug 31 '15 at 11:00
I tried `left`/`right` with `split` and it always returned the 1st value. Just a heads up for anybody out there not noticing that you need to use `find` instead. – logicbloke Jun 24 '19 at 17:35

score 44 · Answer 2 · answered Jul 18 '17 at 16:56

44

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")

answered Jul 18 '17 at 16:56

user3626750

451
4
3

1

if you want to use it in an arrayformula remember to set header to empty : `=query(SPLIT("1.23/1.15", "/"), "SELECT Col1 label Col1 '' ")` – arno Aug 08 '19 at 16:44

score 8 · Answer 3 · answered Mar 19 '19 at 21:47

8

Additionally, if you would like to apply this to a range, you can use:

 =index(split(filter(A2:A,A2:A<>""),"/"),0,2)

answered Mar 19 '19 at 21:47

N_Browder

81
1
4

score 1 · Answer 4 · answered Sep 28 '22 at 02:30

Here the array version that doesn't require a QUERY (proposed by @user3626750 ) and works inside ARRAYFORMULA. For example for extracting the first column of SPLIT result:

=ARRAYFORMULA(IF(A1:A="","", FILTER(SPLIT(A1:A,"/"),{1,0})))

For the second column just replace {1,0} with {0,1}.

Get first item from Split()

4 Answers4

Linked