Excel Challenge

[Rudi]

    
Working ONLY in Excel, ONLY with available Excel tools/features and ONLY on the keyboard, how would you get a clean list of the Postal/Zip codes, as depicted in the "Goal" column, from the addresses in the "Challenge" column, without the use of formulas, macros, add-ins (or flash fill - a new feature available in Excel 2013/2016)?

Assuming the file is currently open and active on the screen, list the necessary shortcut keys in the order (or sequence) necessary to solve this challenge.

Excel Challenge.png

Challenge.xlsx

[TonyE]

Spoiler

May not be the answer you are looking for, but it sticks to your rules.
Type the zip codes in a new column

[Rudi]

Ahhh....that's the rebel answer.
I cannot deny the accuracy of your answer, but I will definitely contest the accuracy of the data capture result.

BTW: Nice to see you on the boards again!

[Becks]

The tedious solution:

Spoiler

Select the first cell then this key sequence - repeat until finished
<F2>
<Shift><ctrl><left>
<Ctrl>C
<Tab>
<Ctrl>V
<Down>
<Left>

[Rudi]

Very tedious indeed, but more accurate than Tony's typing method

Of course the ultimate solution would be to process all codes at once.

[Becks]

Spoiler

<ctrl><Home>
<down>
<Ctrl><Shift><Down>
<ctrl>H
<Alt>n
*
<Alt>a
<Esc>

[Rudi]

Huh?
That deletes everything?
Is that the idea?

[Becks]

Spoiler

There is a space after the asterisk
It will delete any and all characters before a space, including the space

[Rudi]

Woah!!!
I was wondering how that would work.
It indeed deletes everything in front of the codes as the codes themselves do not have spaces after them.

I didn't see this coming!!! Excellent solution Becks!

This was the solution I prepared in the event no one provided something better... (but you win hands down with this!!)
It looks long winded, but it works well and the comments add to it looking complex!

Spoiler

Assuming the file is active and you are in cell A1

1. CTRL+SHIFT+Down Arrow (Select list)
2. ALT+A+E (Access Text to Columns)
3. ENTER (Get to step 2)
4. ALT+S (Select the space delimiter)
5. ENTER twice (close Text to Columns and splits data)
6. CTRL+A (select the entire split content)
7. CTRL+G (or F5) (to access the Go To Dialog)
8. ALT+S (to select Go To Special)
9. ALT+O (to select Constants)
10. ALT+U/G/E (to disable numbers, text and logicals)
11. ENTER (close and apply settings to select constants)
12. CTRL+-(minus) (to access the Delete Cells dialog)
13. ALT+L (or up arrow) (to select "Shift cells left")
14. ENTER (to apply command)
15. CTRL+Home (to select cell A1)
16. CTRL+Spacebar (to select active column A)
17. CTRL+-(minus) (to delete column A)
18. CTRL+Home (optional: to select cell A1 and type heading)

[HansV]

Hi Rudi,

Your method would fail if a cell contains three or more numbers, for example

123 Sunset Drive Apartment 3 Louistown 45678

Becks' method will handle such entries correctly.

[Rudi]

Hans...this is not the Excel forum You can't correct me if I'm the master of this challenge!!!!!!!!! (x 40 Zirconium style!!)

However, I have conceded that Beck is the winner
Well done Becks.

Your prize will be transferred via the appropriate transfer protocol.

[HansV]

My apologies, challenge master!

[Rudi]

Apology accepted.