Find Section of String 1

[Keyster86]

Whom it may concern: I am trying to find the formula for finding specific sections in a string.

My string format:
<USER> ; <MAC> ; <IP> ; <SN> ; <OFFICE> ; <ADMIN>

Example:
john.key ; MAC: 00:18:8B6:29:B6 ; IP: 192.168.1.1 ; SN: ####### ; US ARMY ; SPC John Key

What would be the formula to display just the MAC?
00:18:8B6:29:B6
Similar Excel formula would be:

=RIGHT(MID(A1,FIND("MAC: ",A15),22),17)

What would be the formula to display just the IP?
192.168.1.1

And

What would be the formula to display just the SN?
#######
Similar Excel formula would be:

=RIGHT(MID(F15,FIND("IP: ",F15),29),7)

VBScript code to find strings:

strInformation = "john.key ; MAC: 00:18:8B:D6:29:B6 ; IP: 192.168.1.1 ; SN: ####### ; US ARMY ; SPC John Key"

[COLOR=red] some formulas here [/color]

wscript.echo strMAC
wscript.echo strIP
wscript.echo strSN

V/r,

SPC Key
United States Army

 

[Remou]

It seems that a split on ";" may suit:

strInformation = "john.key ; MAC: 00:18:8B:D6:29:B6 ; IP: 192.168.1.1 ; SN: ####### ; US ARMY ; SPC John Key"

astrInfo = Split(strInformation,";")

For i=0 To UBound(astrInfo)

    If Instr(astrInfo(i),"MAC:")>0 Then
       strMAC=astrInfo(i)
    End If
    
    If Instr(astrInfo(i),"IP:")>0 Then
       strIP=astrInfo(i)
    End If
    
    If Instr(astrInfo(i),"SN:")>0 Then
       strSN=astrInfo(i)
    End If
Next

MsgBox strMac & vbCrLf & strIP & vbCrLf & strSN

 

[Keyster86]

You are awesome...thanks a million.

I made a slight variation to the provided script to get exact information versus having the identifying prefixes. This way - I can compare strings.

strInformation = "john.key ; MAC: 00:18:8B:D6:29:B6 ; IP: 192.168.1.1 ; SN: ####### ; US ARMY ; SPC John Key"

astrInfo = Split(strInformation,";")

For i=0 To UBound(astrInfo)

    If Instr(astrInfo(i),"MAC:")>0 Then
       strMAC=REPLACE(astrInfo(i),"MAC: ","")
    End If
    
    If Instr(astrInfo(i),"IP:")>0 Then
       strIP=REPLACE(astrInfo(i),"IP: ","")
    End If
    
    If Instr(astrInfo(i),"SN:")>0 Then
       strSN=REPLACE(astrInfo(i),"SN: ","")
    End If
Next

MsgBox strMac & vbCrLf & strIP & vbCrLf & strSN

V/r,

SPC Key
United States Army