splitting up an IP address


E

eluehmann

the problem with that is that I need the IP address still. The text t
columns splits it up but I loose the IP. I also need this to do i
automatically
 
Ad

Advertisements

F

Frank Kabel

Hi
a very simple solution would be:
- copy column A first to column B
- apply 'Data - text to columns' on column B
 
R

Ron Rosenfeld

the problem with that is that I need the IP address still. The text to
columns splits it up but I loose the IP. I also need this to do it
automatically.
To do this automatically you need an event macro.

The following macro should do what you want.

Any entry made in column A will be parsed into the adjacent columns (same row)
being split up by periods.

No error checking is done to ensure a valid entry.

The four columns to the right of the target will be CLEARed without warning.
Again, this can be changed if required.

You may change the value of aoi, and also the destination parameter, depending
on your requirements.

You may also add error checking for valid IP type entries, if required for your
application.

To enter this, right click on the sheet tab and select View Code.

Paste the code below into the window opens.

===============================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim aoi As Range
Dim c As Range
Dim i As Integer
Set aoi = [A:A]
Application.EnableEvents = False

On Error Resume Next
If Not Intersect(Target, aoi) Is Nothing Then
For Each c In Target
If Not Intersect(c, aoi) Is Nothing Then
For i = 1 To 4
c.Offset(0, i).Clear
Next i
c.TextToColumns Destination:=c.Offset(0, 1), _
DataType:=xlDelimited, Other:=True, OtherChar:=".", _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4,
1))
End If
Next c
End If

Application.EnableEvents = True
End Sub
================================

HTH,

--ron
 
Ad

Advertisements

D

David McRitchie

You probably do not want to separate it out to separate
columns from the standpoint of being able to sort. It is somewhat
of a pain to sort more than 3 columns. You also said you
wanted to treat is as a IP address and the only way you can
have both is to convert them to text in the form 000.000.000.000
as it is valid to have leading zeros. Personally I would keep
the original column for people to look at and have the helper
column for sorting and possibly label tags. Otherwise Ron
supplied an Event macro to change them as you enter *new*
ones.
Sorting TCP/IP Addresses, and the like
http://www.mvps.org/dmcritchie/excel/sorttcp.htm#normdigits
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

splitting up an IP address 2
splitting up an IP address 1
splitting up an IP address 3
Splitting up address 3
IP ADDRESS MODIFICATION 6
IP Address Validation? 6
Excel IP addresses 5
Sorting by IP address?? 6

Top