Convert AB RSLogix tags

[dkintz]

Good morning!

I have a need to convert Allen Bradley RSLogix tags from one format to another. I can save the tags into a .csv and open in Excel. I would like to write a macro to convert them into another format. Here's an example:

Old format: New Format:

N11:0/0 N11[0].0
N11:0/1 N11[0].1
N11:0/2 N11[0].2

And so on..... basically take everything after the colon and reformat it.

Some tags would not have anything after the dash and would be in the form:

N31:0 to N31[0]
N31:1 N31[1]

And so on......

 

[Dave Patton]

N.B. I am not familiar with the cited tags.

Would this formula work?
=IF(LEN(A2)=7,SUBSTITUTE(SUBSTITUTE(A2,":","["),"/","]."),SUBSTITUTE(A2,":","[")&"]")

 

[NoSparks]

try putting this UDF (user defined function) into a regular code module
then in a cell for new format use a formula like =RSLogic(A2)
where A2 is the cell containing the old formatting.

Public Function RSLogic(str As String) As String
    Dim newStr As String
    Dim ray As Variant
    
    If InStr(1, str, ":") And InStr(1, str, "/") Then
        ray = Split(Replace(str, ":", "/"), "/")
        newStr = ray(0) & "[" & ray(1) & "]." & ray(2)
    ElseIf InStr(1, str, ":") And InStr(1, str, "/") = 0 Then
        ray = Split(str, ":")
        newStr = ray(0) & "[" & ray(1) & "]"
    Else
        newStr = str
    End If
    
    RSLogic = newStr
    
End Function

 

[dkintz]

N.B. I am not familiar with the cited tags.

Would this formula work?
=IF(LEN(A2)=7,SUBSTITUTE(SUBSTITUTE(A2,":","["),"/","]."),SUBSTITUTE(A2,":","[")&"]")

Thanks Dave.

This does work but only for the first 10 (i.e. N11:1/0 thru N11:1/9). After this, the "conversion" goes to N11[1/10] instead of N11[1].10

 

[Scott Huish]

Perhaps:
=IF(A1="","",SUBSTITUTE(SUBSTITUTE(A1,":","["),"/","].")&IF(ISNUMBER(FIND("/",A1)),"","]"))

 

[dkintz]

This works perfectly! It works with both forms of the tags.

I should have mentioned the task so others can benefit from this. When converting an RSLogix 500 project to an RSLogix 5000 project, the addresses must be changed to the newer format. The conversion software is slick and works pretty well. The bad thing is that when the addresses are automatically reformatted in the conversion, the documentation (tag names) get lost. Now a user can export the tag names to a .cvs file, run this script, then import the tag names into the new project.

With a project that has 5000 tags, this saves the user hours. No more line by line conversion.

Thank you so much NoSparks!

 

[dkintz]

Perhaps:
=IF(A1="","",SUBSTITUTE(SUBSTITUTE(A1,":","["),"/","].")&IF(ISNUMBER(FIND("/",A1)),"","]"))

I should mention that this formula also works wonderful. Thank you Scott!

 

[dkintz]

Additional tag type:

If the tag is in the form ::[PLC1]N101:4/11, how would I get it to skip the first two colons and the name in brackets, then work on the last part? Ultimately I would need it to morph from:
::[PLC1]N101:4/11 to ::[PLC1]N101[4].11.
The examples so far are using substitution for the ":" and "/" but this won't work now as it also substitutes the first two colons automatically.
Thanks!

 

[NoSparks]

In case the others don't return using built-in functions...

I think this UDF should handle the examples you've presented.

Public Function RSLogic(str As String) As String

    Dim newStr As String, ray As Variant
    Dim x As Integer, plcStr As String, workStr As String
    
    If Left(str, 3) = "::[" Then
        x = InStr(1, str, "]")
        If x Then
            plcStr = Left(str, x)
            workStr = Mid(str, x + 1)
        End If
    Else
        workStr = str
    End If
    
    If InStr(1, workStr, ":") And InStr(1, workStr, "/") Then
        ray = Split(Replace(workStr, ":", "/"), "/")
        newStr = plcStr & ray(0) & "[" & ray(1) & "]." & ray(2)
    ElseIf InStr(1, workStr, ":") And InStr(1, workStr, "/") = 0 Then
        ray = Split(workStr, ":")
        newStr = plcStr & ray(0) & "[" & ray(1) & "]"
    Else
        newStr = plcStr & workStr
    End If
    
    RSLogic = newStr
    
End Function

 

[Rick Rothstein]

In case the others don't return using built-in functions...

I think this UDF should handle the examples you've presented.

Public Function RSLogic(str As String) As String

    Dim newStr As String, ray As Variant
    Dim x As Integer, plcStr As String, workStr As String
    
    If Left(str, 3) = "::[" Then
        x = InStr(1, str, "]")
        If x Then
            plcStr = Left(str, x)
            workStr = Mid(str, x + 1)
        End If
    Else
        workStr = str
    End If
    
    If InStr(1, workStr, ":") And InStr(1, workStr, "/") Then
        ray = Split(Replace(workStr, ":", "/"), "/")
        newStr = plcStr & ray(0) & "[" & ray(1) & "]." & ray(2)
    ElseIf InStr(1, workStr, ":") And InStr(1, workStr, "/") = 0 Then
        ray = Split(workStr, ":")
        newStr = plcStr & ray(0) & "[" & ray(1) & "]"
    Else
        newStr = plcStr & workStr
    End If
    
    RSLogic = newStr
    
End Function

Your function would not handle this input text correctly...

::[PLC1]N101:4/11

which I extrapolated from the original examples posted by the OP. Here is a much shorter function (note the slight function name change) that returns the same values as your function except that it also correctly handles the above input text as well...

Function RSLogix(S As String) As String
  Dim Begin As String
  If Not S Like "*/*" Then S = S & "]"
  If S Like "::*" Then
    Begin = Left(S, InStr(S, "]"))
    S = Mid(S, InStr(S, "]") + 1)
  End If
  S = Replace(Replace(S, ":", "["), "/", "].")
  RSLogix = Begin & S
End Function