14,954
edits
Split text by symbol: Difference between revisions
Jump to navigation
Jump to search
→Example
No edit summary |
|||
| Line 13: | Line 13: | ||
* 121.512532 | * 121.512532 | ||
MySQL Approach | === MySQL Approach === | ||
# | # to get the first string ( 25.040215 ): {{code | code=SELECT TRIM(SUBSTRING_INDEX('25.040215, 121.512532' ,',', <span style="color:red">1</span>))}} | ||
# | # to get the second string ( 121.512532 ): {{code | code=SELECT TRIM(SUBSTRING_INDEX('25.040215, 121.512532' ,',', <span style="color:red">-1</span>))}} | ||
=== Excel Approach === | |||
<table border="1" style=""> | |||
<tr style="background-color: #555555; color: #ffffff;"> | |||
<th></th> | |||
<th>A</th> | |||
<th>B</th> | |||
<th>C</th> | |||
</tr> | |||
<tr> | |||
<td>1</td> | |||
<td>25.040215, 121.512532</td> | |||
<td><div style="background-color: yellow">25.040215</div></td> | |||
<td><div style="background-color: yellow">121.512532</div></td> | |||
</tr> | |||
</table> | |||
If the data is perfect | |||
# to get the first string ( 25.040215 ): B1 = {{code | code=<span style="background-color: #F6CEF5">MID( A1, 1, FIND("-", A1)-1)</span> }} | |||
# to get the second string ( 121.512532 ): C1 = {{code | code=<span style="background-color: #ccc">MID( A1, FIND(",", A1)+1, LEN(A1))</span> }} | |||
if the data was mixed with empty values. Return empty if the value is empty. | |||
# to get the first string ( 25.040215 ): B1 = {{code | code=IF(ISERROR(<span style="background-color: #F6CEF5">MID(A1, 1, FIND(",", A1)-1)</span>), "", <span style="background-color: #F6CEF5">MID(A1, 1, FIND(",", A1)-1)</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>) }} | |||
[[Category:Programming]] | [[Category:Programming]] | ||
[[Category:Data Science]] | [[Category:Data Science]] | ||
[[Category:MySQL]] | |||
[[Category:Excel]] | |||