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: alt text

FlavorOfLife

Posted 2010-12-06T10:16:43.153

Reputation: 213

1what is the question now? how to put that into excel or ... how to treat the repetition of the string (or the item '01af')? – akira – 2010-12-06T10:27:51.970

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 – 2010-12-06T10:39:06.963

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 – 2010-12-06T11:42:56.757

...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 – 2010-12-06T12:00:12.167

Answers

(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)

Copy data to A1.
Enter in B1: =MID($A$1, ROW(B1)*12-11, 11). Copy it down to B2:B20.
Enter in C1: =MID(B1,10,2)&MID(B1,7,2). Copy it down to C2:C20.
(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}.

wilson

Posted 2010-12-06T10:16:43.153

Reputation: 4 113

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 – 2010-12-08T10:43:27.883

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 – 2010-12-08T10:57:00.487

@Arjan, HEX2DEC() requires Analysis ToolPak add-in installed. That's why I use this hack. (Ref: http://office.microsoft.com/en-us/excel-help/hex2dec-HP005209111.aspx)

– wilson – 2010-12-08T10:58:57.547

@Arjan, thanks for your comment. This is a nice fix! I will update it to my solution later today. – wilson – 2010-12-08T11:00:55.847

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 – 2010-12-08T11:03:47.623

(Note, again, that I am NOT sure what the question asker wants. Maybe it's not one long line at all. Or maybe it's just those 5 groups of 4 values... I see a lower-case 0c in my earlier comment; beware when using that.) – Arjan – 2010-12-08T11:06:00.333

Answer updated. – wilson – 2010-12-09T10:24:53.457

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

Joe Taylor

Posted 2010-12-06T10:16:43.153

Reputation: 11 533

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 – 2010-12-06T11:53:00.600