| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove digits after colon
how to parse list of IPs and delete all digits after the colon (include
":" )? have: 121.12.249.207:1080 187.4.205.153:1080 188.193.79.13:17040 222.174.72.38:1080 need parse and get just IP list, without ":1080", .i.e. 121.12.249.207 187.4.205.153 188.193.79.13 222.174.72.38 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Remove digits after colon
The Excel solution:
Data Text to columns, Delimited, set colon as delimiter. The programming solution: Sub Kill() Dim Cel As Range Dim S() As String For Each Cel In Selection S = Split(Cel.Value, ":") Cel.Value = S(0) Next End Sub HTH. Best wishes Harald "astral" wrote in message ... how to parse list of IPs and delete all digits after the colon (include ":" )? have: 121.12.249.207:1080 187.4.205.153:1080 188.193.79.13:17040 222.174.72.38:1080 need parse and get just IP list, without ":1080", .i.e. 121.12.249.207 187.4.205.153 188.193.79.13 222.174.72.38 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Remove digits after colon
You can use this formula in B1:
=LEFT(A1,SEARCH(":",A1)-1) assuming your first number is in A1, and then copy the formula down. Fix the values and then delete column A. Alternatively, you can highlight column A and click on Data | Text-to- columns, choose delimited on the first panel and colon as the delimiter on the second panel. Click Finish, then delete column B. Hope this helps. Pete On Jul 26, 11:05*am, "astral" wrote: how to parse list of IPs and delete all digits after the colon (include ":" )? have: 121.12.249.207:1080 187.4.205.153:1080 188.193.79.13:17040 222.174.72.38:1080 need parse and get just IP list, without ":1080", .i.e. 121.12.249.207 187.4.205.153 188.193.79.13 222.174.72.38 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Remove digits after colon
Excel solution is simpler. It helps, thank you.
============= "Harald Staff" wrote in message . .. The Excel solution: Data Text to columns, Delimited, set colon as delimiter. The programming solution: Sub Kill() Dim Cel As Range Dim S() As String For Each Cel In Selection S = Split(Cel.Value, ":") Cel.Value = S(0) Next End Sub HTH. Best wishes Harald "astral" wrote in message ... how to parse list of IPs and delete all digits after the colon (include ":" )? have: 121.12.249.207:1080 187.4.205.153:1080 188.193.79.13:17040 222.174.72.38:1080 need parse and get just IP list, without ":1080", .i.e. 121.12.249.207 187.4.205.153 188.193.79.13 222.174.72.38 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Remove digits after colon
This helps, thank you.
-------- "Pete_UK" wrote in message ... You can use this formula in B1: =LEFT(A1,SEARCH(":",A1)-1) assuming your first number is in A1, and then copy the formula down. Fix the values and then delete column A. Alternatively, you can highlight column A and click on Data | Text-to- columns, choose delimited on the first panel and colon as the delimiter on the second panel. Click Finish, then delete column B. Hope this helps. Pete On Jul 26, 11:05 am, "astral" wrote: how to parse list of IPs and delete all digits after the colon (include ":" )? have: 121.12.249.207:1080 187.4.205.153:1080 188.193.79.13:17040 222.174.72.38:1080 need parse and get just IP list, without ":1080", .i.e. 121.12.249.207 187.4.205.153 188.193.79.13 222.174.72.38 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Remove digits after colon
Programming solution considerably faster :-)
This is a Programming newsgroup so I wasn't sure what you wanted. Glad it helped. Best wishes Harald "astral" wrote in message ... Excel solution is simpler. It helps, thank you. ============= "Harald Staff" wrote in message . .. The Excel solution: Data Text to columns, Delimited, set colon as delimiter. The programming solution: Sub Kill() Dim Cel As Range Dim S() As String For Each Cel In Selection S = Split(Cel.Value, ":") Cel.Value = S(0) Next End Sub HTH. Best wishes Harald "astral" wrote in message ... how to parse list of IPs and delete all digits after the colon (include ":" )? have: 121.12.249.207:1080 187.4.205.153:1080 188.193.79.13:17040 222.174.72.38:1080 need parse and get just IP list, without ":1080", .i.e. 121.12.249.207 187.4.205.153 188.193.79.13 222.174.72.38 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Remove digits after colon
You're welcome - thanks for feeding back.
Pete On Jul 26, 4:28*pm, "astral" wrote: This helps, thank you. -------- "Pete_UK" wrote in message ... You can use this formula in B1: =LEFT(A1,SEARCH(":",A1)-1) assuming your first number is in A1, and then copy the formula down. Fix the values and then delete column A. Alternatively, you can highlight column A and click on Data | Text-to- columns, choose delimited on the first panel and colon as the delimiter on the second panel. Click Finish, then delete column B. Hope this helps. Pete On Jul 26, 11:05 am, "astral" wrote: how to parse list of IPs and delete all digits after the colon (include ":" )? have: 121.12.249.207:1080 187.4.205.153:1080 188.193.79.13:17040 222.174.72.38:1080 need parse and get just IP list, without ":1080", .i.e. 121.12.249.207 187.4.205.153 188.193.79.13 222.174.72.38- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how to change colon to semi-colon in CP/List Seprator | Excel Discussion (Misc queries) | |||
| Remove all but the last seven digits in a cell | Excel Discussion (Misc queries) | |||
| Formula amendment to not remove digits | Excel Worksheet Functions | |||
| remove and replace digits from a number | Excel Worksheet Functions | |||
| Remove ' if not followed by two digits | Excel Programming | |||
Linear Mode
