Code Crackers Challenge: Round One

Ok everyone...here's something that I've been wanting to do for some time, but am just finally getting around to: a series on encoding and decoding data.

I've always had an interest in "breaking secret codes" etc. which has led me to creating those secret codes and encryption techniques. Some of you may already know I've done a bit of code-creating. My March Madness bracket spreadsheet is protected using my own code and methodology.

I have a hunch that there's a group of code-cracking geeks out there like me just waiting to be challenged. So here's the challenge:

• I'm going to provide an encrypted message.


• You may ask questions (use the blog's comment feature so all can see and benefit from the answers).


• I'll give answers and hints as I feel appropriate.


• The first person to submit via the comment feature the message "Excel_Geek, I have cracked your code." encoded using the same technique I've used wins. (This helps prevent "brute force" type attacks, which tend to give answers without knowing how they were derived.)

What do you win? I dunno...let's see...how about your pick between two free $50 Projects or 2 free hours of Excel consulting/instruction via Skype? Oh, and you'll obviously also be held out as the Code Cracking Champion (if that's meaningful to you).

So here is the Code Crackers Challenge Round One Encoded Message:

R0#4y,4y, y3'44yudiyI'*/46%my)-48&4x

I also feel compelled to give you a first hint, and it's been encrypted using the same technique (which is also kind of a hint in and of itself!):

P$/.s0+2%,. s12 ./s$%s&. 2,.e

So...pass this along to your code-cracking geek friends and family. Ask questions. I'll do the best i can to respond to each question, though not always as directly as you'll probably hope.

Good Luck!

Excel_Geek

Pull numeric value from string

The other day I had an interesting request for help via my little MeeboMe chat window. James wanted a formula to pull a numeric value from a string contained in another cell. The string was something like this:

"Nylon rope 300Ft"

He had a whole column of this type of data and wanted to pull out the number of feet of each item. The "Ft" would always be immediately after the numeric value desired, and a space would always immediately precede it, but here's the kicker: there could be any number of other spaces before the numeric value and there could also be other number before the numeric value desired.

It took me a bit to get where I needed to be, but here's the logic built into a pretty long, nasty, nested formula:

1. First get the string that contains everything up to and including the "Ft" using LEFT and FIND.


2. Count the number of spaces in the string identified in #1 by comparing the length of that string versus that string if all the spaces are replaced with nothing (""), using LEN and SUBSTITUTE.


3. Replace the last space found in the string identified in #1 with an odd character not likely to be in any of the data (I used a tilde ~) using SUBSTITUTE.


4. Pull out the numeric value, which is the remainder of the string after the tilde using MID, FIND, and LEN.

I put together a sample spreadsheet of this process both broken out into parts and also all nested together in a single formula.

Excel_Geek Insiders, enjoy!

Later,

Excel_Geek