# How to convert a single line of Hex numbers into a multi-row Excel table?

I'm currently at some hex strings in a video game's data files. The structure is:

``````XX 01 YY YY (then repeats)
``````

Where:

``````XX is the item's cost
01 is unknown (it won't matter)
YY YY is the item's ID.
``````

For example:

``````28 01 AF 01
``````

...would be describe an item that costs 40 gold (which is 28 in hex) with an ID of "01AF" (I have a reference list for all the ID/Items in the game).

I want to put this into an excel table with columns:

``````| ItemID | Cost |
``````

How would a super user attack this task if one had something like this:

-
what is the question now? how to put that into excel or ... how to treat the repetition of the string (or the item '01af')? – akira Dec 6 '10 at 10:27
I'm not 100% I got it right, but I fixed the title to better summarize the question. Please use the "edit" link to change it if it's not okay. (Hence: please answer akira's comment by editing the question.) – Arjan Dec 6 '10 at 10:39
Thank you, this is my first time doing this, I'm not quite sure on the terminology itself. All I'm trying to do is extract game data from its hex structure that I've found. – FlavorOfLife Dec 6 '10 at 11:42
...the new example does not make it much clearer to me. First of all, you've selected 21 values? And is it indeed just one line you're processing? – Arjan Dec 6 '10 at 12:00

(EDIT: Solution improved based on Arjan's comment)

Here is a simple but dirty MS Excel solution.

Assumption: Data is in the format `28 01 AF 01 XX XX XX XX YY YY YY YY...` (space separated)

1. Copy data to A1.
2. Enter in B1: `=MID(\$A\$1, ROW(B1)*12-11, 11)`. Copy it down to B2:B20.
3. Enter in C1: `=MID(B1,10,2)&MID(B1,7,2)`. Copy it down to C2:C20.
4. (a) Enter in D1: `=HEX2DEC(LEFT(B1,2))`. Copy it down to D2:D20.
(b) If `#NAME?` error appeared, then enter the following in D1. Copy it down to D2:D20.

``` =IF(CODE(LEFT(B1,1))>64,CODE(LEFT(B1,1))-55,LEFT(B1,1))*16 +IF(CODE(MID(B1,2,1))>64,CODE(MID(B1,2,1))-55,MID(B1,2,1))```

(Another assumption for solution (b): all the HEX values are in capital letter)

Column C & D would be what you need.

## Explanation

B:B breaks data (A1) into different rows.

C1 simply extracts the characters in position {10, 11, 7, 8} from B1.

D1 extracts the first 2 characters in A1, and then convert it from HEX to DEC.

Note: Analysis ToolPak add-in is required for `HEX2DEC()` in (a). If `#NAME?` error appears, it means that the function is not supported. In this case, the hack in (b) would help to convert from HEX to DEC.

`CODE()` gets the ASCII code of the character. For ABCDEF, their ASCII codes are {65,66,67,68,69,70}. `CODE()-55` then returns {10,11,12,13,14,15}.

-
I think things start with one big row, not 20 rows. But +1 for the hex-to-decimal hack. Excel supports `=HEX2DEC(LEFT(A1,2))` though ;-) – Arjan Dec 8 '10 at 10:43
And though I cannot upvote twice: if you're into it, then note that `=MID(\$A\$1, (ROW(A2)-2)*12+1, 11)` will give you each set of 4 digits, if this formula is in cell `A2` and below, and `A1` holds one long string like `12 01 CF 01 0A 01 CE 01 23 01 CD 01 0c 01 D5 01 19 01 0A 01` as seen in the screen capture... – Arjan Dec 8 '10 at 10:57
@Arjan, HEX2DEC() requires Analysis ToolPak add-in installed. That's why I use this hack. (Ref: office.microsoft.com/en-us/excel-help/hex2dec-HP005209111.aspx) – wilson Dec 8 '10 at 10:58
@Arjan, thanks for your comment. This is a nice fix! I will update it to my solution later today. – wilson Dec 8 '10 at 11:00
Amai, I didn't know that `HEX2DEC` needs that. On the other hand: it's easily enabled by clicking some menu on a default installation, isn't it? (I never understood why this is a separate thingy. I've to admit I use OpenOffice.org, which apparently has it enabled by default, if one can even disable it there.) Enjoy! – Arjan Dec 8 '10 at 11:03

It's a bit of a confuzzled way to do it but it works.

Copy the file into MS Word.
Start the cursor at the start of the file.
Write a macro that moves the cursor right 12 places and then enters a line break (return)
Save the file in a text format, make sure Line Breaks are turned on.
Open the file in Excel, making sure to select the output in the cells to be text and not general (or you'll use the leading 0's)

Then simply add the Headers and remove the column you don't want / need. Voila.

Perhaps someone with better VB knowledge can help you here, the syntax is completely different to C++ and I'm a bit pressed for time today.

The code I have is:

``````(Pseudocode) - While Not End of File

Selection.MoveRight Unit:=wdCharacter, Count:=12
Selection.TypeParagraph
Wend
``````
-
Thanks so much, I'll set things up while you code up :D – FlavorOfLife Dec 6 '10 at 11:43
My VB has failed. It's 99% of the way there. Just setting up the While Not end of file loop. It's flummoxed me sorry. If you knew the number of Hex entries you could do a do or while loop setting the counter to that number and just decrement the counter. – Joe Taylor Dec 6 '10 at 11:53