|This page contains or refers to some text pages containing VBA macros and User Defined Functions. If you need assistance to install or to use a macro or function please refer to my «Getting Started with Macros« or delve into it deeper on my Install page.|
Frequently you have a column of codes that have 1 or more alpha characters followed by numeric digits. the ALPHA_N function will create a string that can be sorted. The new column should be formatted as TEXT. The example here is formatted as 8 characters.l
The ALPHA_N functions described above and other macros and functions described on this page can be viewed/downloaded here code/sorttcp.txt
Sorting in Excel is limited to 3 columns when invoked; and, while it is possible to sort more columns by invoking the sort more than once, for most purposes this is not very practical.
|TCP/IP number||Reformated for sorting|
The subroutines and functions that reformat TCP/IP addresses for sorting or return them to their normalized addressing can viewed/downloaded here code/sorttcp.txt -- Myrna Larson posted enhanced code for Excel 2000 and up using Split and Join VBA functions.
These subroutines or the method be applied to outline numbering systems -- 1, 1.1, 1.11, 1.111, 1.112, 1.113 -- but have since created a ChaptSort macro shown later, which should work better for outline numbering.
IPSORT and IPNORM are functions used like Worksheet Functions.
The example below shows how the F column is expanded with IPSort, or contracted with IPNorm. The disadvantage is that you are using two columns. You can see these functions in the User Defined Functions in the Paste Formula Wizard (Shift + F3) the toolbar button looks like [fx]. Select a cell, shift+F3, you will see an equal sign on the formula bar, enter a cellname into the dialog box.
Subroutines: IPSortSUB, IPNormSUB, IP2Text for European usage can be used to change the values inplace without using a secondary cell. Assuming you installed these functions and macros. Select cells e20:e22 then run a macro that you find with ALT+F8 Functions do not show there
More information on finding Functions and Subroutines in my
Show FORMULA of another cell in Excel
which includes information on installing Functions and Subroutines.
IPv4 Address to Decimal Integer (IPv4 Address is in cell B1)
=((VALUE(LEFT(B1, FIND(".", B1)-1)))*256^3)+((VALUE(MID(B1, FIND(".", B1)+1, FIND(".", B1, FIND(".", B1)+1)-FIND(".", B1)-1)))*256^2)+((VALUE(MID(B1, FIND(".", B1, FIND(".", B1)+1)+1, FIND(".", B1, FIND(".", B1, FIND(".", B1)+1)+1)-FIND(".", B1, FIND(".", B1)+1)-1)))*256)+(VALUE(RIGHT(B1, LEN(B1)-FIND(".", B1, FIND(".", B1, FIND(".", B1)+1)+1))))
Decimal Integer to IPv4 Address (Decimal Integer is in cell B2)
=IF(B2<>"", CONCATENATE(INT(B2/256^3), ".", INT(MOD(B2, (256^3))/(256^2)), ".", INT(MOD(MOD(B2, 256^3), 256^2)/256), ".", MOD(MOD(MOD(B2, 256^3), 256^2), 256)), "")
The following code allows sorting of an unknow number of nodes. A text sort will accomplish the sorting. Code below can also be found in viewed/downloaded from code/sorttcp.txt
Function ChaptSort(cell As String) As String 'dmcritchie, worksheet.functions, 2004-01-21, _ ' #sAGbiE4DHA.2388@TK2MSFTNGP09.phx.gbl Dim i As Long, j As Long, n As Long Dim oldstr As String, newstr As String oldstr = cell i = 1 newstr = "" reloop: j = InStr(Mid(oldstr, i), ".") If j > 5 Then ChaptSort = "#segment" Exit Function ElseIf j <> 0 Then l = j - l newstr = newstr & "." & Left("0000", 5 - j) _ & Mid(oldstr, i, j - 1) i = i + j GoTo reloop Else If Len(oldstr) - i >= 4 Then ChaptSort = "#length" Exit Function Else newstr = newstr & "." & Left("0000", _ 3 - (Len(oldstr) - i)) & Mid(oldstr, i) End If End If ChaptSort = "*" & Mid(newstr, 2) End Function
In this more generalized function, the numbers will be normalized at 3 digits, which is also the default. All non digits will be passed through directly.
NormDigits Example can also be used for TCP/IP addresses
220.127.116.11 001.001.001.001 =personal.xls!NormDigits(A1) 18.104.22.168 031.032.034.250 =personal.xls!NormDigits(A2)Function NormDigits(cell As String, Optional p As Long) As String 'dmcritchie, worksheet.functions, 2004-01-21, _ ' #sAGbiE4DHA.2388@TK2MSFTNGP09.phx.gbl 'dmcritchie, newuser, 2004-07-01 Dim i As Long, n As String, s As String Dim newstr As String s = UCase(Trim(cell)) If p = 0 Then p = 3 newstr = "" n = "" reloop: For i = 1 To Len(s) If Mid(s, i, 1) Like "[0-9]" Then n = n & Mid(s, i, 1) ElseIf n = "" Then newstr = newstr & Mid(s, i, 1) Else newstr = newstr & Format(n, Left("0000000", p)) n = "" newstr = newstr & Mid(s, i, 1) End If Next i If n <> "" Then newstr = newstr & Format(n, Left("0000000", p)) NormDigits = newstr End Function
ChemNDigits Example with Chemical Nomenclature
The numbers will be normalized at 3 digits, which is also the default. Usage is dependent on proper use of letter case for Elements.
CHNO C-001H-001N-001O-001 =personal.xls!ChemNDigits(A11) C3H2 C-003H-002 =personal.xls!ChemNDigits(A12) C6H12NO2 C-006H-012N-001O-002 =personal.xls!ChemNDigits(A13) C10H5F3 C-010H-005F-003 =personal.xls!ChemNDigits(A14) C12H6FO2 C-012H-006F-001O-002 =personal.xls!ChemNDigits(A15) C12H6F3 C-012H-006F-003 =personal.xls!ChemNDigits(A16) Cs4H20 Cs004H-020 =personal.xls!ChemNDigits(A17)
To print Cs4H20 as Cs4H20 you can use a subroutine posted by David Hagar, programming, 2002-01-22Function ChemNDigits(cell As String, Optional p As Long) As String ' David McRitchie, newusers, 2004-07-01 ' http://www.mvps.org/dmcritchie/sorting.htm & tcpip.htm ' http://google.com/groups?threadm=uNAGaA5XEHA.2840@TK2MSFTNGP11.phx.gbl Dim i As Long, n As String, s As String, c As String Dim newstr As String s = Trim(cell) If p = 0 Then p = 3 newstr = "" n = 0 '------ end of initialization c = Left(s, 1) For i = 2 To Len(s) If Mid(s, i, 1) Like "[A-Z]" Then If c <> "" Then newstr = newstr & Left(c & "--", 2) End If n = Application.WorksheetFunction.Max(1, n) newstr = newstr & Format(n, Left("0000000", p)) n = 0 c = Mid(s, i, 1) ElseIf Mid(s, i, 1) Like "[a-z]" Then c = c & Mid(s, i, 1) ElseIf Mid(s, i, 1) Like "[0-9]" Then If c <> "" Then newstr = newstr & Left(c & "--", 2) n = n & Mid(s, i, 1) c = "" Else newstr = newstr & "..error.." End If Next i If c <> "" Then newstr = newstr & Left(c & "--", 2) n = Application.WorksheetFunction.Max(1, n) newstr = newstr & Format(n, Left("0000000", p)) ChemNDigits = newstr 'Chemical Nomenclature End Function
Domain name within email addressUse a helper column to obtain the domain name:
Domain name within urlSome wiki notes on Host name and on Domain name. One cannot actually tell what the server host name is just by looking at a url but the following will work for what it would appear to be in the US, some attempt has been made to make it work with two letter country codes. I used the following to create a domain column based on the host column and does what I want it to do for me in the US. It doesn't actually work well with state, county, and city goverments using ".us" but it works fine for companies, and learning institutions (in the US).Function StrDomain(cell As Range) As String Dim newstr As String Dim i As Integer, j As Integer, k As Integer newstr = Replace(cell, "//", ".") j = 1: k = 0 For i = 2 To Len(newstr) If i + 2 = Len(newstr) Then GoTo done If Mid(newstr, i, 1) = "." Then k = j j = i + 1 End If Next i done: StrDomain = Mid(cell, k + 1, Len(cell) - k) End Function
Visit [my Excel home page] [Index page] [Excel Onsite Search] [top of this page]
Please send your comments concerning this web page to: David McRitchie send email comments
Copyright © 1997 - 2004, F. David McRitchie, All Rights Reserved