Check out some similar questions!

nigel_cro · Dec 3, 2009, 08:25 AM

Hi Guys,

Noob on this site so forgive any indiscretions. I have found a number of posts here already which go part way to answering my problem, but I am now left with a specific issue which I am sure someone on here (JB? ) can answer with relative ease.

I need to take a HEX string (eg D0 BF FF FF) and turn it to little endian (eg FF FF BF D0) in Excel. I have got part way to a VB scripted module but I really don't know enough VB to even know where I am going wrong.

Any suggestions would be gratefully received.

All the best,

Nigel

JBeaucaire · Dec 3, 2009, 11:25 AM

If your text string in A2 is shown exactly like you demonstrated above with spaces between each quad, then use this worksheet formula to reverse the order of the quads:

=RIGHT(A2,2) & MID(A2,6,4) & MID(A2,4,3) & LEFT(A2,2)

If your data does not match this exact format, click on GO ADVANCED and use the paperclip icon to upload a sample worksheet with several examples of your data and your desired results.

nigel_cro · Dec 3, 2009, 02:27 PM

JB,

Thanks for the really prompt response.

I knew I would get something wrong - I would like to input the HEX string as a continuous string, it makes the inputting process a whole lot easier, to have to include spaces would build unnecessary effort and room for error into the process.

So, the actual input will look like [D0BFFFFF] and the output would look similar [FFFFBFD0]. Just to emphasise, both input and output would be in continuous strings.

I'm sorry to have wasted your time, thanks for the help.

All the best,

Nigel

KISS · Dec 3, 2009, 03:26 PM

I'm not sure what JB did wrong. He just assumes the inputted string is a fixed length and gives you the functions to create an output string of the same length in the proper order.

If you wanted to reverse an entire file, say something like INTEL hex format, then that's something a little different,

The functions should be usable based on our understanding of the problem.

JBeaucaire · Dec 3, 2009, 05:18 PM

Hopefully you'll pull these two examples apart and see what's going on so you can use this technique yourself in the future.

This is for reversing your text string 2 characters at a time with no spaces:

=RIGHT(A1,2) & MID(A1,5,2) & MID(A1,3,2) & LEFT(A1,2)

nigel_cro · Dec 4, 2009, 12:59 AM

Excellent! Thanks JB, it works a treat. I would still love to know where I am going wrong in my attempts to write a scripted solution in a module, but that can wait for a while, I guess I will have to get the books out over Xmas.

Once again, thanks for your help.

All the best,

Nigel

JBeaucaire · Dec 4, 2009, 02:36 AM

I tend to rule out VBA for tasks that can be handled automatically with worksheet formulas.

Glad I could help.

nigel_cro · Dec 4, 2009, 03:48 AM

JB,

Just one more question please...

Is there a decent reference work that you would recommend for dealing with Excel and all of it's functionalities? Something that will beef up my knowledge of formulas and will lead me on to working with VBA to compile modules.

Thanks again,

Nigel

JBeaucaire · Dec 4, 2009, 11:57 AM

Nope, I've never read a book so couldn't recommend one. Nothing is more educational than real-world need coupled with a forum or two of people willing to help you work out complicated real-world scenarios.