Previous in Forum: SchemaCrawler   Next in Forum: Installing an Operating System without a CD
Close
Close
Close
7 comments
Power-User

Join Date: Dec 2005
Location: Willenhall, UK
Posts: 160

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.

Comments rated to be Good Answers:

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

Comments rated to be "almost" Good Answers:

Check out these comments that don't yet have enough votes to be "official" good answers and, if you agree with them, rate them!
Guru
United Kingdom - Member - Not a New Member Hobbies - Musician - New Member Hobbies - Fishing - New Member

Join Date: May 2006
Location: Reading, Berkshire, UK. Going under cover.
Posts: 9687
Good Answers: 467
#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
Power-User

Join Date: Dec 2005
Location: Willenhall, UK
Posts: 160
#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
3
Guru
United Kingdom - Member - Not a New Member Hobbies - Musician - New Member Hobbies - Fishing - New Member

Join Date: May 2006
Location: Reading, Berkshire, UK. Going under cover.
Posts: 9687
Good Answers: 467
#3
In reply to #2

Re: Excel 2007 ASCI converter

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:

Input1st char2nd charCode of 1st charCode of 2nd char256 X code(2nd char)+code(1st char)
3M3M517719763

Here are the formulae:

Input1st char2nd charCode of 1st charCode of 2nd char256 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 19763d = 4D33h

Hope this helps.

__________________
"Love justice, you who rule the world" - Dante Alighieri
Register to Reply Good Answer (Score 3)
Guru
United Kingdom - Member - Not a New Member Hobbies - Musician - New Member Hobbies - Fishing - New Member

Join Date: May 2006
Location: Reading, Berkshire, UK. Going under cover.
Posts: 9687
Good Answers: 467
#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
Power-User

Join Date: Dec 2005
Location: Willenhall, UK
Posts: 160
#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
Guru
Hobbies - Musician - Engineering Fields - Chemical Engineering - New Member Engineering Fields - Control Engineering - New Member Engineering Fields - Instrumentation Engineering - New Member

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
Guru
United Kingdom - Member - Not a New Member Hobbies - Musician - New Member Hobbies - Fishing - New Member

Join Date: May 2006
Location: Reading, Berkshire, UK. Going under cover.
Posts: 9687
Good Answers: 467
#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
Register to Reply 7 comments
Interested in this topic? By joining CR4 you can "subscribe" to
this discussion and receive notification when new comments are added.

Comments rated to be Good Answers:

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

Comments rated to be "almost" Good Answers:

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

Users who posted comments:

JohnDG (4); Mikerho (1); peterd (2)

Previous in Forum: SchemaCrawler   Next in Forum: Installing an Operating System without a CD

Advertisement