Menu
Split(char[], StringSplitOptions) Method This method is used to splits a string into substrings based on the characters in an array. You can specify whether the substrings include empty array elements. Syntax: public String[] Split(char[] separator, StringSplitOptions option);
You can use SPLIT to split a string into a list of words, for example, or into a list of numbers. To use SPLIT, enter the string you want to split into the function's parentheses, and then specify the delimiter you want to use to separate the string into substrings.
We can use a formula that is based on the INDEX and MATCH functions to lookup a value in a table in a Google Sheet. Unlike in excel where the result is entered with CRTL+SHIFT+ENTER, the result is entered with the enter key only for Google Sheets.
The quickest and easiest way to apply a formula to an entire column is to: Click the column header for the column you want to apply the formula to. Type the formula you wish to use into the FX bar and press enter. Press Ctrl+D on your keyboard Ctrl+Enter works too.
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"))
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")
Additionally, if you would like to apply this to a range, you can use:
=index(split(filter(A2:A,A2:A<>""),"/"),0,2)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With