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......

 

[dkintz]

@ dkintz

You've lost me with post #18 as both Rick and my functions convert your examples as requested.

You say The addresses then have, but these examples would indicate the concatenation done prior to the format conversion.
What exactly is the issue?

It would be advantages to share your Excel file, redacted, of course.
[/QUOTE

I don't know how to post the actual file but here is the raw data. The issue with NoSparks arrise at the N121:12/0 address
Raw data:
PLC addresses:
N11:12/0
N11:12/1
N11:12/2
N11:12/3
N121:12/0
N121:12/1
N121:12/2
N11:30
N11:31
N11:32
N121:30
N121:31
N121:32
Same addresses for the HMI:
::[AnyName]N11:12/0
::[AnyName]N11:12/1
::[AnyName]N11:12/2
::[AnyName]N11:12/3
::[AnyName]N121:12/0
::[AnyName]N121:12/1
::[AnyName]N121:12/2
::[AnyName]N11:30
::[AnyName]N11:31
::[AnyName]N11:32
::[AnyName]N121:30
::[AnyName]N121:31
::[AnyName]N121:32

 

[dkintz]

Rick, yes your function does do all the forms correctly. Thank you very much!

 

[Rick Rothstein]

@ dkintz

You've lost me with post #18 as both Rick and my functions convert your examples as requested.

I just ran you code against the data the OP posted in Message #21 and compared your results against mine and found some discrepancies. I than debugged your code against one of those varying results and found a problem with one of your code lines, namely, this one...

If InStr(1, workStr, ":") And InStr(1, workStr, "/") Then

VBA's And operator performs its operation on the binary numbers involved, not on the logical equivalents of those numbers. So if the two InStr functions returned, say, 5 and 8, then this operation...

5 And 8

because 5 equals 101 in binary notation and 8 equals 1000 in binary notation, so this...

(101)b And (1000)b

would evaluate to 0, not the True that you would be expecting. Had the InStr results been 4 and 7, then your code would work because And'ing those two numbers produce a non-zero result which your If..Then statement would have interpreted as True. This is why your function worked most of the time, but not all of the time.

Whenever you And or Or two functions that return numbers, you must create a comparison for each number so that the And or Or is operating on True and False values. So, if you change the above line of code to this, your UDF will return the same values as my UDF for the values the OP posted...

If (InStr(1, workStr, ":") > 0) And (InStr(1, workStr, "/") > 0) Then

 

[NoSparks]

Thank you Rick, now I understand.
Very much appreciate you taking the time to educate me.

I have a few of your gems tucked away for future reference.
I thank you for being here and being so anxious to share your knowledge.

NoSparks