Excel Translation Forumlae (split from Unitrunker thread)

[Jay911]

For those who wonder what I'm talking about in the message below - in the thread on Unitrunker Beta 6 in the Trunk Decoders forum, I posted a code snippet to be used with Excel or OpenOffice Calc, to allow one to convert between full decimal values (which Unitrunker saves MPT IDs in) and the prefix-ID format more understandable to the MPT user.

As it turns out, I was able to spend some time on it this morning after all... give these a try.

From decimal to 000-0000 format:

=TEXT(INT(B2/8192),"000")&"-"&TEXT(MOD(B2,8192),"0000")

It will not stop you from typing in something that will be greater than a 3 digit prefix. I.e. "500000000" will get you "61035-1280". In other words, it doesn't check for a valid MPT talkgroup ID.

To go back from any dashed ID format to decimal, use this one:

=LEFT(B2,FIND("-",B2)-1)*8192)+(MID(B2,FIND("-",B2)+1,LEN(B2))

This one is a little more tolerant of widths and such .. doesn't matter if it's 2-203 or 002-0203 or 000000002-00000000203 or whatever.

Again, both these assume that the value you want to convert from is in cell B2.

[cg]

thanks for the formula

One very minor thing for those who will cut and paste the first formula, add an = to the front of it.

Excel certainly eliminates a lot of back and forth with the Calculator program.

chris

[Jay911]

Originally Posted by cg

One very minor thing for those who will cut and paste the first formula, add an = to the front of it.

Thanks, I fixed that just as you were posting.

Originally Posted by cg

Excel certainly eliminates a lot of back and forth with the Calculator program.

You should see what I can do with Motorola TypeII vs TypeI (after finding someone else's hard work and adapting it).

[morfis]

Originally Posted by Jay

=TEXT(INT(B2/8192),"000")&"-"&TEXT(MOD(B2,8192),"0000")

It will not stop you from typing in something that will be greater than a 3 digit prefix. I.e. "500000000" will get you "61035-1280". In other words, it doesn't check for a valid MPT talkgroup ID.

Why didn't I think of TEXT...thanks Jay (not that I use excel for doing this!)

Isn't it good the way Excel simplifies things - mmmmm

.......morfis wanders off slapping his forehead

[Jay911]

I had to look it up in the Help myself. Of all the things I can say about Microsoft, the help system for Office XP is pretty good.

[Comint]

Jay

Thanks for the formulae. So easy when you know how.

Comint