Excel Challenge
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Excel Challenge
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.
You do not have the required permissions to view the files attached to this post.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 3StarLounger
- Posts: 361
- Joined: 24 Jan 2010, 14:24
- Location: Buckinghamshire, England
Re: Excel Challenge
Spoiler
May not be the answer you are looking for, but it sticks to your rules.
Type the zip codes in a new column
Type the zip codes in a new column
Tony
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Excel Challenge
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!
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!
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 2StarLounger
- Posts: 196
- Joined: 31 Mar 2011, 03:41
- Location: Perth, Western Australia
Re: Excel Challenge
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>
<F2>
<Shift><ctrl><left>
<Ctrl>C
<Tab>
<Ctrl>V
<Down>
<Left>
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Excel Challenge
Very tedious indeed, but more accurate than Tony's typing method
Of course the ultimate solution would be to process all codes at once.
Of course the ultimate solution would be to process all codes at once.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 2StarLounger
- Posts: 196
- Joined: 31 Mar 2011, 03:41
- Location: Perth, Western Australia
Re: Excel Challenge
Spoiler
<ctrl><Home>
<down>
<Ctrl><Shift><Down>
<ctrl>H
<Alt>n
*
<Alt>a
<Esc>
<down>
<Ctrl><Shift><Down>
<ctrl>H
<Alt>n
*
<Alt>a
<Esc>
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Excel Challenge
Huh?
That deletes everything?
Is that the idea?
That deletes everything?
Is that the idea?
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 2StarLounger
- Posts: 196
- Joined: 31 Mar 2011, 03:41
- Location: Perth, Western Australia
Re: Excel Challenge
Spoiler
There is a space after the asterisk
It will delete any and all characters before a space, including the space
It will delete any and all characters before a space, including the space
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Excel Challenge
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!
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
- CTRL+SHIFT+Down Arrow (Select list)
- ALT+A+E (Access Text to Columns)
- ENTER (Get to step 2)
- ALT+S (Select the space delimiter)
- ENTER twice (close Text to Columns and splits data)
- CTRL+A (select the entire split content)
- CTRL+G (or F5) (to access the Go To Dialog)
- ALT+S (to select Go To Special)
- ALT+O (to select Constants)
- ALT+U/G/E (to disable numbers, text and logicals)
- ENTER (close and apply settings to select constants)
- CTRL+-(minus) (to access the Delete Cells dialog)
- ALT+L (or up arrow) (to select "Shift cells left")
- ENTER (to apply command)
- CTRL+Home (to select cell A1)
- CTRL+Spacebar (to select active column A)
- CTRL+-(minus) (to delete column A)
- CTRL+Home (optional: to select cell A1 and type heading)
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78686
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Excel Challenge
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.
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.
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Excel Challenge
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.
However, I have conceded that Beck is the winner
Well done Becks.
Your prize will be transferred via the appropriate transfer protocol.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78686
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Excel Challenge
Apology accepted.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.