I. Intro
The SPLIT function divides the text of a string into substrings, which are placed into separate cells in the same row.
II. About the function
- Formula: =SPLIT(text, delimiter, [split_by_each], [remove_empty_text])
- Arguments:
- text (required): The string that will be divided.
- delimiter (required): The character or characters that will divide the text.
- split_by_each (optional): Determine whether delimiter characters are considered separately or together.
- True or Null: Each delimiter will split the string.
- False: Consecutive delimiters are treated as one delimiter.
- remove_empty_text (optional): Determine whether empty texts are removed from the split results.
- True or blank: Treat continuous spaces as a single delimiter. If the "split_by_each" is selected as True, this is equivalent to selecting False for "split_by_each".
- False: Add blank cell values between continuous separators. If the "split_by_each" parameter is selected as False, the effect with this parameter is equivalent to selecting the "split_by_each" parameter as True.
- Example: =SPLIT("1,2,,3", ",",True,False) will split 1, 2, blank, and 3 into four separate cells.
III. Steps
Use the SPLIT function
- Select a cell and enter =SPLIT.
- Enter the arguments in brackets and press Enter. The example below shows how phone numbers separated by "/" are split.
250px|700px|reset
Delete the SPLIT function
Select the cell with the SPLIT function and press Delete.
IV. Use cases
Extracting values with the SPLIT and INDEX functions
- Scenario: HR maintains a sheet where employee details are stored as "Name - Employee ID". They need to separate the name and employee ID into two different cells.
- Formula: INDEX(SPLIT(A2, "-"), 1), INDEX(SPLIT(A2, "-"), 2)
- Description:
- SPLIT(A2, "-"): This breaks the text in cell A2 into parts using the "-" as the divider.
- INDEX(SPLIT(A2, "-"), 1): Takes the result from the SPLIT function as the reference range and extracts the first part, which is the name.
- INDEX(SPLIT(A2, "-"), 2): Takes the result of the SPLIT function as the reference range of the INDEX function and extracts the second part, which is the employee ID.
250px|700px|reset