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
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.TextToColumns Destination:=c.Offset(0, 1), _
DataType:=xlDelimited, Other:=True, OtherChar:=".", _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4,
Application.EnableEvents = True
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*
Sorting TCP/IP Addresses, and the like http://www.mvps.org/dmcritchie/excel/sorttcp.htm#normdigits