I would like to be able to extract a part of a string (IP address) and strip out everyhting around a certains value. For example, I have an IP address 192.168.200.34, is it possible to only extact the 200 part and drop things either side including the periods (.)?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
extracting part of a string 2
- Thread starter Alanclyde
- Start date
-
1
- #2
- Thread starter
- #3
I have trid that, but all IP address are not the same, 192.19.100.12
192.168.10.101
So in the examples above I want to extract the 100 in the first line and extract the 10 in the second, with the characters either side (including the periods) to be deleted.
192.168.10.101
So in the examples above I want to extract the 100 in the first line and extract the 10 in the second, with the characters either side (including the periods) to be deleted.
- Thread starter
- #5
Still no luck I am afraid. I have to be truthful at this point and mention taht I am not a VB expert and I believe SPLIT is a VB function.
I am trying to do this this in a query or Event Procedure within Access.
I understand there is a similar way of doing this in Excel, but alas I am unable to fathom this one out.
I am trying to do this this in a query or Event Procedure within Access.
I understand there is a similar way of doing this in Excel, but alas I am unable to fathom this one out.
Split is VBA and will work nicely in an event procedure. Post a little more about the event you want to use.
- Thread starter
- #7
I have a query in Access which for this example has one field, IP_Address. I can issue the like function as below:
Like *.*.40.* and this will show me all IP_Addresses on the 40 Lan.
Likewise, Like *.*.200.* will show all the IP_Addresses on the 200 Lan.
As there are many of them and they could change, it would not be possible to issue the Like function as I did above as this would be very time consuming. As new ones are added and old ones removed, I would ike a function that would alwasy look at the third set of characters which denote the LAN part of an IP Address so I could perform calculations on each of those numbers.
The results I am after MAY be like this show below:
Lan Number Of
10 20
120 53
200 47
Does this make sense?
Like *.*.40.* and this will show me all IP_Addresses on the 40 Lan.
Likewise, Like *.*.200.* will show all the IP_Addresses on the 200 Lan.
As there are many of them and they could change, it would not be possible to issue the Like function as I did above as this would be very time consuming. As new ones are added and old ones removed, I would ike a function that would alwasy look at the third set of characters which denote the LAN part of an IP Address so I could perform calculations on each of those numbers.
The results I am after MAY be like this show below:
Lan Number Of
10 20
120 53
200 47
Does this make sense?
How about a user defined function?
In a standard module, paste this:
The SQL then looks like this:
In a standard module, paste this:
Code:
Function ExtractPart(sIP)
If Len(sIP) - Len(Replace(sIP & "", ".", "")) > 2 Then
ExtractPart = Split(sIP, ".")(2)
Else
ExtractPart = ""
End If
End FunctionThe SQL then looks like this:
Code:
SELECT ExtractPart(t.IP) AS Lan, Count(t.ID) AS Nmbr
FROM Table t
GROUP BY ExtractPart(t.IP)- Thread starter
- #9
-
1
- #10
Alanclyde,
Since Remou is "a star", you should thank him by awarding a star.
Duane
Hook'D on Access
MS Access MVP
Since Remou is "a star", you should thank him by awarding a star.
Duane
Hook'D on Access
MS Access MVP
- Thread starter
- #11
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question