HEX2DEC whole row

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

SC - 23 Apr 2008 00:18 GMT

I have never been to this newsgroup and I am astounded by the
plethra of post on here. I look forward to using this resource.

I have a task I have not been able to complete.

I have an excel spread sheet that contains several colums. I would
say each colum has at least 1000 lines. There is one particular
column that has numbers in HEXADECIMAL format. Due to the enormous
amount of data contained in this column it is not feasible for me to
take each number and manually conver them to DECIMAL.

I want to know if there is a formula I can plug in for the whole
column that when I envoke it ...it will convert each line in that
column from hex to decimal. I have seen the hex2dec formaul listed
in the program and have been able to run it however it only does one
line.

Being a newbie I am requesting step by step instructions (if possible)
of how to achieve this result.

I appreciate it and thanks so much.

SC
Raleigh, N.C.
scederas<at>gmail.com

Reply to this Message

Dave Peterson - 23 Apr 2008 01:14 GMT

You could insert a new column directly to the column that holds the data to
convert.

Then you can put the formula in the topmost cell (row 1 or 2???) and fill down
the column.

You only type the formula once and then let excel adjust it as you fill down
that column.

Debra Dalgleish shares some ways to fill a range:
http://contextures.com/xlDataEntry01.html
http://contextures.com/xlDataEntry01.html#Quick (using the keyboard)
http://contextures.com/xlDataEntry01.html#Mouse (using the mouse)

> I have never been to this newsgroup and I am astounded by the
> plethra of post on here. I look forward to using this resource.
[quoted text clipped - 21 lines]
> Raleigh, N.C.
> scederas<at>gmail.com

Signature

Dave Peterson

Reply to this Message

SC - 23 Apr 2008 05:52 GMT

Thank you for your reply.

I have a problem with the formula. When I click the top of the row I
choose insert fuction...then I get to choose "HEX2DEC" and then I get
asked to put in a number for the function argument. I do not know
what to put there nor do I know how to procede and complete the
implementation of the formula.

Help !

SC
Raleigh, N.C.
scederas<at>gmail.com

>You could insert a new column directly to the column that holds the data to
>convert.
[quoted text clipped - 35 lines]
>> Raleigh, N.C.
>> scederas<at>gmail.com

Reply to this Message

Dave Peterson - 23 Apr 2008 13:19 GMT

Say your range of data is in A1:A10 (a small subset)
Insert a new column B and put this in B1:
=HEX2DEC(A1)

Then drag down from B1 to B10.

For users of xl2003 and below:

Remember that you and all of the people you're sharing this workbook with have
to have the analysis toolpak installed (Tools|Addins).

> Thank you for your reply.
>
[quoted text clipped - 49 lines]
> >> Raleigh, N.C.
> >> scederas<at>gmail.com

Signature

Dave Peterson

Reply to this Message

SC - 24 Apr 2008 05:00 GMT

Man I hate to keep bugging you about this. I guess I am not following
your intructions.

>Say your range of data is in A1:A10 (a small subset)
>Insert a new column B and put this in B1:
>=HEX2DEC(A1)

Okay I did this

>Then drag down from B1 to B10.

I am not sure what you mean by this. After entering the formual in B1
I think try to drag down and get nothing that resembles being able to
select multiple fields, etc.
To be specific about my data ....I have data in A2 through...at least
a thousand entries. A1 is the column heading. Do I place the formula
there or to the first line that has data which is A2.

Thanks

>For users of xl2003 and below:
>
[quoted text clipped - 54 lines]
>> >> Raleigh, N.C.
>> >> scederas<at>gmail.com

Reply to this Message

Gord Dibben - 24 Apr 2008 05:09 GMT

Enter the formula in B2 as =HEX2DEC(A2)

Hover your mouse pointer over the bottom right corner of B2 untill you see a
black cross.

Left-Click on the black cross and drag down column B.

The A2 cell reference will change to A3, A4, A5 etc. as you drag/copy down the
column.

Make sure Calculation is set to automatic under Tools>Options>Calculation.

Gord Dibben MS Excel MVP

>Man I hate to keep bugging you about this. I guess I am not following
>your intructions.
[quoted text clipped - 73 lines]
>>> >> Raleigh, N.C.
>>> >> scederas<at>gmail.com

Reply to this Message

SC - 25 Apr 2008 08:13 GMT

Okay...stupid here again....when I enter the formula below and move
the mouse down after seeing the black cross ....all the numbers in the
column change to #NUM! instead of changing to the decimal number.

I just dont know what is going on.

Thanks,

Sam

>Enter the formula in B2 as =HEX2DEC(A2)
>
[quoted text clipped - 87 lines]
>>>> >> Raleigh, N.C.
>>>> >> scederas<at>gmail.com

Reply to this Message

Dave Peterson - 25 Apr 2008 11:59 GMT

Copy a few of the values that you have in column A. Maybe they're not real hex
numbers.

Or maybe they're too long (more than 10 characters) for =hex2dec() to use?

> Okay...stupid here again....when I enter the formula below and move
> the mouse down after seeing the black cross ....all the numbers in the
[quoted text clipped - 97 lines]
> >>>> >> Raleigh, N.C.
> >>>> >> scederas<at>gmail.com

Signature

Dave Peterson

Reply to this Message

Gord Dibben - 25 Apr 2008 18:14 GMT

The workbook you sent me.............un-solicited, I might add, which is why you
did not get a reply...........shows this formula in C2

=DEC2HEX(B2-800000) and the value of 800001 in B2 which returns 1

In C9 =DEC2HEX(B9-800000) with the value of 800043 in B9 which returns 2B

None of your formulas return the #NUM! error for me.

Gord

>Okay...stupid here again....when I enter the formula below and move
>the mouse down after seeing the black cross ....all the numbers in the
[quoted text clipped - 97 lines]
>>>>> >> Raleigh, N.C.
>>>>> >> scederas<at>gmail.com

Reply to this Message

SC - 25 Apr 2008 21:58 GMT

Why post your emial address on your posting then.
I do not understand why you are bringing up the subject of
unsolicited. I sent you an email because I did not want to post the
item on here.
I apologize if I have offended you in any way. I would have prefered
an email stating that you are upset with the fact that I sent you
something via emai. What did you hope to accomplish by not replying
to the email? Obviously I know what your email address is.

You have been helpfull in previous post. For you to reply back on a
public forum that you are upset makes no since to me.

Oh well....like I said I apologize if you were offended by my actions.
I meant no harm.

Sam

>The workbook you sent me.............un-solicited, I might add, which is why you
>did not get a reply...........shows this formula in C2

Reply to this Message

Peo Sjoblom - 25 Apr 2008 23:17 GMT

You could have sent an email asking if you could send a workbook?
Just because someone has an email doesn't mean it is OK to send files

Signature

Regards,

Peo Sjoblom

> Why post your emial address on your posting then.
> I do not understand why you are bringing up the subject of
[quoted text clipped - 16 lines]
>>why you
>>did not get a reply...........shows this formula in C2

Reply to this Message

Gord Dibben - 25 Apr 2008 23:29 GMT

I do not post my email address on my posting.

I post a munged version of my email address so that if I ask for an email I can
tell poster how to un-mung and send to me.

Not too many of us in these news groups expect nor accept personal emails.

I was just pointing that out as the protocol on these groups.

What I hope to accomplish is to remind others that my in-box gets full enough
without more unsolicited stuff.

The workbook you sent me has what looks like decimals in Column B

800001
800033
800035
800037

Your formulas in Column C use the =DEC2HEX() function.

Do you want to go other way round?

Use the HEX2DEC function in Column C?

Gord

>Why post your emial address on your posting then.
>I do not understand why you are bringing up the subject of
[quoted text clipped - 15 lines]
>>The workbook you sent me.............un-solicited, I might add, which is why you
>>did not get a reply...........shows this formula in C2

Reply to this Message

SC - 27 Apr 2008 23:31 GMT

>I post a munged version of my email address so that if I ask for an email I can
>tell poster how to un-mung and send to me.

I have to reply .....you mean to tell me that you think that someone
will have to ask you how to send an email to you based on the format
in which you post your email address in your headers? CMON dude try
that on someone else.

>Not too many of us in these news groups expect nor accept personal emails.

I don't know about that. I am 45 years old and have been emailing
people from the email address listed in their header for many years
now. You are the first person I have ever had a problem with.

>I was just pointing that out as the protocol on these groups.

Oh.....let me check with the server that houses this newsgroup...wait
a minute....it is housed in a virtual world.

>What I hope to accomplish is to remind others that my in-box gets full enough
>without more unsolicited stuff.

More un-solicited stuff....unless I have caused you some unheard of
grief I fail to see how the email I sent to you which was specifically
sent to you for a specific reason which was to attain help from a post
you posted on a public newsgroup forum for the whole world to
see.....falls into the same category as mail you do not want to see.
Sorry it was not an email asking you to buy Viagra.

Reply to this Message

SC - 25 Apr 2008 22:01 GMT

In C9 =DEC2HEX(B9-800000) with the value of 800043 in B9 which
returns 2B

>None of your formulas return the #NUM! error for me.

Excuse me. All data in COLUMN B is Hexadecimal. They need to be
converted to DECIMAL. That is what I indicated in my email.

Regards

Reply to this Message