Excel 2007 ASCI Converter

7 comments

peterd Power-User Join Date: Dec 2005 Location: Willenhall, UK Posts: 159	Excel 2007 ASCI Converter 02/11/2010 4:00 AM
	Does anyone know of an ASCI to decimal or hex converter for Excel. I use the standard maths formula HEX2DEC at present but have to convert the original ASCI code to hex by hand -ok for a few codes but I now have to do 2k+ at a time. __________________ A day without a smile is a day lost
	Register to Reply

Interested in this topic? By joining CR4 you can "subscribe" to
this discussion and receive notification when new comments are added.

These comments received enough positive votes to make them "good answers".

Check out these comments that don't yet have enough votes to be "official" good answers and, if you agree with them, vote them!

JohnDG Guru Join Date: May 2006 Location: Reading, Berkshire, UK. Going under cover. Posts: 9684 Good Answers: 468	#1 Re: Excel 2007 ASCI converter 02/11/2010 4:11 AM
	Try =CODE(). It works in 2003. __________________ "Love justice, you who rule the world" - Dante Alighieri
	Register to Reply

peterd Power-User Join Date: Dec 2005 Location: Willenhall, UK Posts: 159	#2 In reply to #1 Re: Excel 2007 ASCI converter 02/11/2010 5:01 AM
	Thanks JohnDG I did not describe my problem correctly. The ASCI codes I am reading are split into 2 digit registers. eg 3MBR32 = 3M,BR,32. Then 3M = 334D hex = D433 hex with LSB and MSB swapped as this is how the plc sees it. so 3M = D433 hex = 19763 decimal. I need the excel to convert ASCI to DEC with the LSB/MSB swap over - is this possible? __________________ A day without a smile is a day lost
	Register to Reply

Guru

Join Date: May 2006

Location: Reading, Berkshire, UK. Going under cover.

Posts: 9684

Good Answers: 468

#3
In reply to #2

02/11/2010 5:31 AM

I can do most of it, but 2003 doesn't seem to have the DEC2HEX function (I'd have to do it in VBA).

This gets the decimal version of what you want:

Input	1st char	2nd char	Code of 1st char	Code of 2nd char	256 X code(2nd char)+code(1st char)
3M	3	M	51	77	19763

Here are the formulae:

Input	1st char	2nd char	Code of 1st char	Code of 2nd char	256 X code(2nd char)+code(1st char)
3M	=MID(A3,1,1)	=MID(A3,2,1)	=CODE(B3)	=CODE(C3)	=E3*256+D3

Calculator tells me that 19763_d = 4D33_h

Hope this helps.

__________________
"Love justice, you who rule the world" - Dante Alighieri

No more Good Answers. Go to first "Almost" Good Answer

JohnDG Guru Join Date: May 2006 Location: Reading, Berkshire, UK. Going under cover. Posts: 9684 Good Answers: 468	#4 In reply to #3 Re: Excel 2007 ASCI converter 02/11/2010 5:44 AM
	FYI, here it is in one statement (assuming again that the input data is in cell A3): =(CODE(MID(A3,2,1))*256)+CODE(MID(A3,1,1)) __________________ "Love justice, you who rule the world" - Dante Alighieri
	Register to Reply Score 1 for Good Answer

peterd Power-User Join Date: Dec 2005 Location: Willenhall, UK Posts: 159	#5 In reply to #3 Re: Excel 2007 ASCI converter 02/11/2010 6:24 AM
	Thanks JohnDG. 2007 has HEXDEC. __________________ A day without a smile is a day lost
	Register to Reply

Mikerho Guru Join Date: Jan 2007 Location: Moses Lake, WA, USA, Thulcandra - The Silent Planet (C.S. Lewis) Posts: 4216 Good Answers: 194	#6 In reply to #3 Re: Excel 2007 ASCI converter 02/11/2010 9:51 AM
	Hi JohnDG, I remember my Excel 97 included all the number base converting functions, but they weren't installed initially. I had to go to the add-ins and install them manually as I also did with solver. Oh, and GA! Mike __________________ "Reason is not automatic. Those who deny it cannot be conquered by it. Do not count on them. Leave them alone." - Ayn Rand
	Register to Reply Score 1 for Good Answer

JohnDG Guru Join Date: May 2006 Location: Reading, Berkshire, UK. Going under cover. Posts: 9684 Good Answers: 468	#7 In reply to #6 Re: Excel 2007 ASCI converter 02/11/2010 9:59 AM
	Nice one, Mike - worked a treat, thanks! GA back at ya! __________________ "Love justice, you who rule the world" - Dante Alighieri
	Register to Reply