Split text by symbol

Split text by symbol (another string, also called delimiter or separator)

Approaches

list of equivalent approaches to split text by symbol

• Excel: (1) LEFT, RIGHT, FIND, LEN (2) MOD, FIND, LEN
• PHP: explode function
• MySQL: MySQL SUBSTRING_INDEX() function - w3resource

Example

Task: Split 25.040215, 121.512532 by the comma ( , )

Expected output:

• 25.040215
• 121.512532

MySQL Approach

1. to get the first string ( 25.040215 ): SELECT TRIM(SUBSTRING_INDEX('25.040215, 121.512532' ,',', 1))
2. to get the second string ( 121.512532 ): SELECT TRIM(SUBSTRING_INDEX('25.040215, 121.512532' ,',', -1))

Excel Approach

	A	B	C
1	25.040215, 121.512532	25.040215	121.512532

If the data is perfect

1. to get the first string ( 25.040215 ): B1 = MID( A1, 1, FIND("-", A1)-1)
2. to get the second string ( 121.512532 ): C1 = MID( A1, FIND(",", A1)+1, LEN(A1))

if the data was mixed with empty values. Return empty if the value is empty.

1. to get the first string ( 25.040215 ): B1 = IF(ISERROR(MID(A1, 1, FIND(",", A1)-1)), "", MID(A1, 1, FIND(",", A1)-1))
2. to get the second string ( 121.512532 ): C1 = IF(ISERROR(TRIM(MID(A1, FIND(",", A1)+1, LEN(A1)))), "", TRIM(MID(A1, FIND(",", A1)+1, LEN(A1)))

online demo (allow to edit)