+ Reply to Thread
Results 1 to 18 of 18

Converting HEX value to BIT-fields

  1. #1
    Registered User
    Join Date
    12-09-2019
    Location
    pakistan
    MS-Off Ver
    2010
    Posts
    7

    Converting HEX value to BIT-fields

    Hi

    If I have a hex value "AC23", whose 16-bit binary is "1010110000100011" and decimal is "44067". How can I break it into following groups of bits (bit-fields):

    (5-bits)--(1-bit)--(5-bits)--(5-bits)
    10101 -- 1 -- 00001-- 00011
    21 -- 1 -- 1 -- 3

    Is there any formula by which I can break a HEX value into its bits as above and then I can get those values in columns as shown in attached image?

    bitf.jpg

  2. #2
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

    Re: Converting HEX value to BIT-fields

    Do you absolutely need to do this with formulas or would a VBA macro be acceptable?

    For whichever answer you give me for the above question, what is the range of Hex numbers that can appear in a cell?

    Also, will you need to do this for only cell A1 or for a range of cells in Column A?

  3. #3
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,329

    Re: Converting HEX value to BIT-fields

    Please Login or Register  to view this content.
    or
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    or
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    or
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by BMV; 02-01-2020 at 12:37 PM.

  4. #4
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

    Re: Converting HEX value to BIT-fields

    Assuming the value in cell A1 is always 4 hex-digits long, put these formulas in the indicated cells (and copy down if necessary)...

    B1: =BIN2DEC(LEFT(HEX2BIN(LEFT(A1,2),8),5))

    C1: =0+MID(HEX2BIN(LEFT(A1,2),8),6,1)

    D1: =BIN2DEC(MID(HEX2BIN(LEFT(A1,2),8)&HEX2BIN(RIGHT(A1,2),8),7,5))

    E1: =BIN2DEC(RIGHT(HEX2BIN(RIGHT(A1,2),8),5))
    Last edited by Rick Rothstein; 02-02-2020 at 02:33 AM.

  5. #5
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,329

    Re: Converting HEX value to BIT-fields

    oh. I forgot about 2nd parameter.
    Rick Rothsteini, I thin it's typo =0+MID(HEX2BIN(LEFT(A1,2),8),6,1)
    Last edited by BMV; 02-02-2020 at 01:13 AM.

  6. #6
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

    Re: Converting HEX value to BIT-fields

    Quote Originally Posted by BMV View Post
    Rick Rothsteini, I thin it's typo =0+MID(HEX2BIN(LEFT(A1,2),8),6,1)
    Yes, it was a typo... I have corrected it in Message #4 so it now reads correctly. Thank you for catching that.

  7. #7
    Registered User
    Join Date
    12-09-2019
    Location
    pakistan
    MS-Off Ver
    2010
    Posts
    7

    Re: Converting HEX value to BIT-fields

    Thanks BMV

    Can you explain a bit about the first of the two options that you have provided?

  8. #8
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,329

    Re: Converting HEX value to BIT-fields

    linko, Use Rick Rothstein formulas. However
    My first variant the same as
    D1: =BIN2DEC(MID(HEX2BIN(LEFT(A1,2),8)&HEX2BIN(RIGHT(A1,2),8),7,5)) and only last numbers 7 and 5 should be change according you rules (5-bits)--(1-bit)--(5-bits)--(5-bits). So it looks like universal.
    The second option is only short way.

  9. #9
    Registered User
    Join Date
    12-09-2019
    Location
    pakistan
    MS-Off Ver
    2010
    Posts
    7

    Re: Converting HEX value to BIT-fields

    Can you tell any specific reason why you suggesting to use Rick Rothstein's formulas?
    Last edited by AliGW; 02-02-2020 at 05:49 AM. Reason: Please don't quote unnecessarily!

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,382

    Re: Converting HEX value to BIT-fields

    Rick's fcormulae are more compact, therefore easier to maintain going forward.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  11. #11
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,329

    Re: Converting HEX value to BIT-fields

    Quote Originally Posted by linko View Post
    Can you tell any specific reason why you suggesting to use Rick Rothstein's formulas?
    not always shorter formula better from performance point of view, but if it possible to use one standard function in stand of two or more it's best way.

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,382

    Re: Converting HEX value to BIT-fields

    I didn't say "shorter" - I said "more compact". Not the same thing.

  13. #13
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,329

    Re: Converting HEX value to BIT-fields

    AliGW. it was additional comment only.

  14. #14
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: Converting HEX value to BIT-fields

    Just came here on the back of the reverse question I answered. Perhaps more compact formulas with minimal calculations are:

    Please Login or Register  to view this content.
    WBD
    Office 365 on Windows 11, looking for rep!

  15. #15
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: Converting HEX value to BIT-fields

    Sorry. Just amusing myself now. Pure bit manipulation (old programmers and all that ...):

    Please Login or Register  to view this content.
    :D

    WBD

  16. #16
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

    Re: Converting HEX value to BIT-fields

    Quote Originally Posted by WideBoyDixon View Post
    [code]
    =BITRSHIFT(BITAND(HEX2DEC(A1),BITLSHIFT(31,11)),11)
    My version of Excel (2010) does not have those first two functions.

  17. #17
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,329

    Re: Converting HEX value to BIT-fields

    BITAND and BITRSHIFT only from Excel 2013

  18. #18
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: Converting HEX value to BIT-fields

    Ah. OK.

    BITLSHIFT(number,shift) = number*2^shift
    BITRSHIFT(number,shift) = INT(number/2^shift)

    so...

    Please Login or Register  to view this content.
    is

    Please Login or Register  to view this content.
    As I said, I was just messing around with bits and bobs ...

    :D

    WBD

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 27
    Last Post: 03-29-2019, 10:47 PM
  2. [SOLVED] Formatting a time field when converting .csv to .xlsx and changing fields to text only
    By bvwalker1 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-09-2019, 09:51 AM
  3. Replies: 3
    Last Post: 06-02-2018, 02:17 PM
  4. [SOLVED] Help with converting table fields into a new table based on cell blank or not blank
    By BG1983 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-15-2016, 01:00 AM
  5. Problem with converting date fields
    By Hondahawkrider in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 07-07-2015, 03:22 PM
  6. [SOLVED] Help converting fields with * and spaces to email addresses
    By aprice88 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-25-2012, 10:25 PM
  7. Converting fields to date in design view
    By GeoGreco in forum Access Tables & Databases
    Replies: 2
    Last Post: 03-25-2009, 12:29 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1