Modulus 10 (4 registers) Assembly in Excel

Hi there,

I'm struggling with a project right now that involves collecting data from power meters in load centers. The data format is Modulus 10 - 4 Registers (Mod10L4), and it is collected via a Modbus TCP/IP OPC.

Initial attempt was to retrieve these numbers in RSView 32, but immediately that proved to be difficult as RSView32 is not setup to deal with that type of dataformat.

Because I really only need these numbers in excel, I have chosen to use an OPC client that allows me to import numbers to excel, but again I'm stuck at the obstacle where the OPC only wants to grab the first register or the first two registers as either an integer or real number. So my option now is grab all four registers individually and re-assemble the Mod 10 number in excel.

I suspect this cannot be accomplished by the function commands internal to excel and will probably involve visual basic code. Has anybody else attempted such a task? If so, how'd you go about it? Thanks.

Seemed to have made some progress today for those interested...

Simply imported each register individually into cells and assembled the cells as you would assemble text... =A1&""&A2&""&A3&""&A4 for the four registers.

But you have to dodge the obstacle where your leading zeros drop off so you don't lose your place value. I could only do this by using 4 rows, each to do a If/then expression checking to see if my value was greater than 999, 99, then 9, and adding a leading "0" in each case, again using the text assembly expression above.

That creates a 16-digit text value, which by multiplying by 1 in an equation in another cell produces your actual 16 digit number that excel recognizes as a number. Took 19 cells to produce 1 number! But seems to work and updates in realtime with the OPC I'm using.

PittsEngineer said:

But you have to dodge the obstacle where your leading zeros drop off so you don't lose your place value. I could only do this by using 4 rows, each to do a If/then expression checking to see if my value was greater than 999, 99, then 9, and adding a leading "0" in each case, again using the text assembly expression above.

Click to expand...

Hi

if I understood the problem correct, you could try something like

=TEXT(A1;"0000") & Text(A2, "0000") .......

where the format specifier "0000" guarantees that also small numbers have 4 digits e.g. 50 --> 0050

maybe you can make your excel sheet a bit more readable...

br
bb

That cleaned my spreadsheet up very nicely. Thanks, now I just need to create a macro to move populate a row of cells and time-stamp it on command and I've created a monthly log of power usage by load center and MCC. Slick.

Hi,

You could try this too:
=A1*10000+A2*10000^2+A3*10000^3

Matthew