Customer name coder for Cmd 770 1

[OzzieGeorge]

As you all know using matworks to program a station name has the pitfall that if you enter the phone type wrong it can screw things up. For this reason I always programme station names using Cmd 770. Now most people hate using this because of the need to convert the name to a hexadecimal code so I suppose its time I gave away a shortcut. I often give this spreadsheet away to customers who administer the names on their own systems so why shouldn't I give it to you guys.

in Excel cell A1 type "Type name below here" in B1 Type "Code will appear below here"

Then copy the rather lengthy formula below to B2

=IF(LEN(A2)>16,"Too many Characters. Must be 16 or less",IF(ISNA(A2),"",IF(A2>"",DEC2HEX(CODE(MID(A2,1,1)),2),"")&IF(LEN(A2)>1,DEC2HEX(CODE(MID(A2,2,1)),2),"")&IF(LEN(A2)>2,DEC2HEX(CODE(MID(A2,3,1)),2),"")&IF(LEN(A2)>3,DEC2HEX(CODE(MID(A2,4,1)),2),"")&IF(LEN(A2)>4,DEC2HEX(CODE(MID(A2,5,1)),2),"")&IF(LEN(A2)>5,DEC2HEX(CODE(MID(A2,6,1)),2),"")&IF(LEN(A2)>6,DEC2HEX(CODE(MID(A2,7,1)),2),"")&IF(LEN(A2)>7,DEC2HEX(CODE(MID(A2,8,1)),2),"")&IF(LEN(A2)>8,DEC2HEX(CODE(MID(A2,9,1)),2),"")&IF(LEN(A2)>9,DEC2HEX(CODE(MID(A2,10,1)),2),"")&IF(LEN(A2)>10,DEC2HEX(CODE(MID(A2,11,1)),2),"")&IF(LEN(A2)>11,DEC2HEX(CODE(MID(A2,12,1)),2),"")&IF(LEN(A2)>12,DEC2HEX(CODE(MID(A2,13,1)),2),"")&IF(LEN(A2)>13,DEC2HEX(CODE(MID(A2,14,1)),2),"")&IF(LEN(A2)>14,DEC2HEX(CODE(MID(A2,15,1)),2),"")&IF(LEN(A2)>15,DEC2HEX(CODE(MID(A2,16,1)),2),"")))


You will then need to ensure you have the following add-ins ticked in the tools add-ins menu

Analysis toolpak
Analysis toolpak - VBA

Then if you type a name in A2 press enter or click elsewhere the code you need will appear.

Hope this helps ease the job. I may even later let you in on how to write a script that uses this to enter all the names at once if the customer supplies you with an extension-name list.

 

[pave35]

Excellent! Well done.

 

[OzzieGeorge]

Were the instructions adequate?

Did it work for you?

 

[OzzieGeorge]

Ok now if you know anything about scripts then you should know that if you can get the list of names - extension numbers from the customer in excel format it is easy to utilise this formula to create a script for the mach script editor simply create a spreadsheet with the cmd number (770) in the A column for the number of extensions you are dealing with then paste the extn number column into the B column (you might want to reverse the two operations to make life easier) you then copy the names into column D then modify the fomula so that it is in column C but acting on column D. Use the fill feature to extend the formula to apply to all the extns you want to programme put a star "*" in column E and extend that in the same way as you just did the formula and this can then be saved as a .csv file.

The .csv file can then be imported into the mach script editor and run as a script.

Just out of interest if you can write macros you can automate all of this (I have written one which creates the script for names and also speed dials with names) however I intend to hold onto my edge for a little while longer.

I have opened the door its up to you if you want to step through.

By the way less than about 4 names and you might as well just use the formula as originally posted and enter the results manually.

just as an aside if anyone can think of anything they would like to see automated I am open to suggestions.

 

[pave35]

Works like a champ!!!
Easy Campese!!!