Split text by symbol: Difference between revisions
Jump to navigation
Jump to search
| Line 40: | Line 40: | ||
# to get the second string ( 121.512532 ): C1 = {{code | code=IF(ISERROR(TRIM(<span style="background-color: #ccc">MID(A1, FIND(",", A1)+1, LEN(A1))</span>)), "", TRIM(MID(<span style="background-color: #ccc">A1, FIND(",", A1)+1, LEN(A1))</span>) }} | # to get the second string ( 121.512532 ): C1 = {{code | code=IF(ISERROR(TRIM(<span style="background-color: #ccc">MID(A1, FIND(",", A1)+1, LEN(A1))</span>)), "", TRIM(MID(<span style="background-color: #ccc">A1, FIND(",", A1)+1, LEN(A1))</span>) }} | ||
[https://docs.google.com/spreadsheets/d/11VeZIxGR7orL4smf_0G6oi7VxxNi3FFsCL9b6QrLhBE/edit?usp=sharingc online demo] (allow to edit) | |||
<div style="float:left; padding-right:5px;"> | |||
{{#widget:Iframe | |||
|url=https://docs.google.com/spreadsheet/ccc?key=11VeZIxGR7orL4smf_0G6oi7VxxNi3FFsCL9b6QrLhBE | |||
|width=900 | |||
|height=500 | |||
|border=1 | |||
}} | |||
[[Category:Programming]] | [[Category:Programming]] | ||
[[Category:Data Science]] | [[Category:Data Science]] | ||
[[Category:MySQL]] | [[Category:MySQL]] | ||
[[Category:Excel]] | [[Category:Excel]] | ||
Revision as of 11:49, 18 April 2015
Split text by symbol (delimiter / separator)
Approachs
- Excel: (1) LEFT, RIGHT, FIND, LEN (2) MOD, FIND, LEN
- PHP: explode
- MySQL: MySQL SUBSTRING_INDEX() function - w3resource
Example
Task: Split 25.040215, 121.512532 by the comma ( , )
Expected output:
- 25.040215
- 121.512532
MySQL Approach
- to get the first string ( 25.040215 ):
SELECT TRIM(SUBSTRING_INDEX('25.040215, 121.512532' ,',', 1)) - 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
- to get the first string ( 25.040215 ): B1 =
MID( A1, 1, FIND("-", A1)-1) - 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.
- to get the first string ( 25.040215 ): B1 =
IF(ISERROR(MID(A1, 1, FIND(",", A1)-1)), "", MID(A1, 1, FIND(",", A1)-1)) - 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)