Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question
1  
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
add comment

2 Answers 2

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

share|improve this answer
    
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
1  
@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
show 2 more comments

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
share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.