Hexadecimal to text/decimal

Hex to decimal is clear. But what do you mean by HEX to text?
and could you attached sample file (check yellow banner at the top of the page).

For name, it looks like it should be as simple as =CHAR(HEX2DEC(B5)) copied across.

As near as I can tell, many of the other fields will just be HEX2DEC() function to convert from hexadecimal number to a decimal number.

Some fields appear to need to split the hexadecimal string into 2 bytes (LEFT(hex#,1) and RIGHT(hex#,1)), then use that result in a lookup function based on the table below INDEX(leftcolumn,MATCH(partialhex#,rightcolumn,0))

A couple of fields have you convert a number from HEX2DEC, then apply a HEX2DEC() multiplier. For example S5 =HEX2DEC(S4) and T5 =S5+256*HEX2DEC(T4)

I'm not sure how much detail you need help with, but it appears to basically come down to using the HEX2DEC() function to get the decimal value from row 4, then use that value to get the desired result.

I'm not sure I understand your question about the names. I put =CHAR(HEX2DEC(B4)) into B5. Copied/pasted/filled this formula into C5:P5, which gives the name KEN GRIFFEY JR in the output field (each character in its own cell). If you decide you need to join the characters together into a single text string, then you can use TEXTJOIN() or CONCAT() functions (assuming they are available to you. MSFT sometimes hides these newer functions behind subscription walls).

This has been unbelievably helpful. Thanks a lot.

Originally Posted by MrShorty

Some fields appear to need to split the hexadecimal string into 2 bytes (LEFT(hex#,1) and RIGHT(hex#,1)), then use that result in a lookup function based on the table below INDEX(leftcolumn,MATCH(partialhex#,rightcolumn,0))

Yes you're absolutely right about needing to split the byte into two values. I'm honestly a little lost on how I would enter this formula into a cell (for example, in Q5) to reference the table below though...

EDIT: I guess I'm not sure how I would combine the result of the left and right function to reference a lookup function.

It's not clear to me exactly what the output in Q5 would be. Based on what I understand:

1) A LEFT() function to get the first digit of the hex number LEFT(Q4,1)
1a) The output of the LEFT() and RIGHT() functions is a text string. For Excel, the text string "4" is different from the number 4, so you need to make sure that you are comparing the correct value. Either make sure that all of your numbers in S8:S10 are really text strings (preceded by an apostrophe when entering) or change the text from the LEFT() function to a real number. Using the latter strategy via the VALUE() function VALUE(LEFT(...))
2) Then a MATCH() function to find which row matches that number. MATCH(VALUE(...),S8:S10,0) This will return the row # that matches the first digit or N/A if the first digit doesn't match anything.
3) Then an INDEX() function to find return the corresponding bat -- hand text from Q8:Q10 =INDEX(Q8:Q10,MATCH(...))

That will tell you which hand they bat with. The position played is similar =INDEX(Q13:Q21,MATCH(VALUE(RIGHT(Q4,1)),S13:S21,0))

From there I'm not sure if you want to try to combine bat hand and position into a single text string in Q5 (Q5 =INDEX(bat hand stuff)&INDEX(position stuff), or if you want bat hand and position in separate cells or just what you want to do.

Your revised file did not attach.

You have not included the 3rd argument in the MATCH() function, so MATCH() takes the VALUE(T4) number (the number 2, if the same input data as your original file in post #3) and compares it to the value in AA18. If VALUE(T4) is smaller than AA18, it returns N/A.

My guess is that the values in AA18:AA21 are actually text strings, and we come back to the point I made in step 1a of post #8. Any number will always be smaller than any text string (the number 2 will be smaller than text strings like "01" or "02"), and thus, you get N/A errors for this. Look very carefully at what is in T4 and what is in AA18:AA21 and make sure you are comparing the same "type" of thing -- text or number.

As I suspected, when I open your file, the first thing I notice are green triangles on the AA18:AA21 and Y18:Y21 values. The error is "number stored as text". As explained, the number 2 (from VALUE(T4)) is smaller than the text string "0", so the MATCH() returns N/A.

Two possible solutions -- which depends entirely on why you stored the "numbers" in AA18:AA21 as text strings and not numbers.

Perhaps you are considering these as hexadecimal numbers (so they want to be stored as text) because in some variations these might be real hex numbers. If this is the case, then you need drop the VALUE() part inside of the MATCH() function and add a HEX2DEC() on the outside HEX2DEC(INDEX(Y18:Y21,MATCH(T4,AA18:AA21)))

Or you need to store the numbers in AA18:AA21 (and Y18:Y21) as real decimal numbers not text strings, then use the formula as is.

Where you want to add it to the hex number in S4 (after converting to decimal), I would probably prefer the latter approach, so that you don't end up with difficulties at the final addition step, but it is entirely up to you. The key is to keep careful track of when you are working with hexadecimal text strings and when you are working with decimal numbers and make sure you don't "cross compare" the two.

I can think of no good reason for Excel to have trouble with a decimal number formatted as "00" so it displays the leading 0. Number formatting on Y18:Y21 and AA18:AA21 also should have no impact on whether the lookup function in S5 works or not. Is S4 working correctly now, or is there still some debugging to do? I notice that you are searching for the number 2 in AA18:AA21 which contain 0, 256, 512, 768. I wonder if you intend to search Y18:Y21 for the number 2, then return the value from AA18:AA21.

Yep that's exactly what I want to do actually.

I am... a flipping moron. Thanks a lot for pointing that out.

Okay I've attached another revised version of the sheet to this message. Now I'm trying to have excel look up the hex from Q25 and match it to what is displayed in the table at J29. I'm able to successfully pull data from the first two columns of the table, but how do I modify the formula to pull data from the entire table? I basically want it to match the hex data from Q25 with the hex lists in K30, M30, O30 and Q30 and the pull the corresponding B/T values in J30, L30, N30 and P30. I'm thinking I need to redo the table somehow...

If I understand, I would have expected the lookup table to be a long, 2 column table rather than 8 columns wide. Move L31:M61 down to J62. Repeat for columns N to Q. Then your lookup function need only search column K until it finds the correct value, then return the value from column J.

Does that seem correct, or did I misunderstand something?

Okay, the masterpiece is almost complete lol. I have another question. As you know, I'm working with a string of hex data. When I paste it into a row, it automatically assigns one hex nibble to a cell (and it unmerges merged cells). Is there a way to prevent it from doing this? In some instances, I want one nibble to occupy a merged cell that might take up two or more columns. Thanks!

Most experienced spreadsheet users avoid merged cells (probably for just these kinds of reasons), so I think most of the suggestions you will get from us will be the opposite of what you say you want. We will try to talk you out of using those merged cells.

We will probably need you to explain exactly what is happening with this paste/import operation and what advantage you feel you are getting from the merged cell(s). Then we can talk about better options or help with merging those cells or something.