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

Question

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:

Answer 1

(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}.

Answer 2

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