Remove digits after colon

[astral]

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

 

[Harald Staff]

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

 

[Pete_UK]

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

 

[astral]

Excel solution is simpler. It helps, thank you.

=============

 

[astral]

This helps, thank you.

--------


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

 

[Harald Staff]

Programming solution considerably faster
This is a Programming newsgroup so I wasn't sure what you wanted. Glad it
helped.

Best wishes Harald

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete

This helps, thank you.

--------


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




- Show quoted text -