Re: Reversing word order in Excel 2007 cells

Subject: Re: Reversing word order in Excel 2007 cells
From: Ned Bedinger <doc -at- edwordsmith -dot- com>
To: techwr-l -at- lists -dot- techwr-l -dot- com
Date: Tue, 13 Apr 2010 12:25:01 -0700

On 04/12/2010 08:56 AM, Ken Poshedly wrote:
> Hey gang,
>
> Does anybody familiar with the inner workings of Excel 2007 so much so that they know if there is a way to change the word order within a cell?

Advisory: Long detailed mechanistic description of how to do this. Sent
to list solely for interested parties. Advanced users and English majors
would rather do almost anything (e.g., have dry heaves) than read this,
so please skip or delete if your vagus nerve starts twitching. I'll
understand.

I don't have Excel so I can't check for you, but someone somewhere has
probably developed an add-in to do this. Rather than looking far and
wide for native or add-in capabilities, consider creating a simple tool
to handle the regular "two word, just reverse them" items. That'd save
you some time compared to manually repeating the steps.

In the context of what you're trying to do with a bunch of regular and
irregular text manipulation tasks, you would spend days and weeks
developing the logic to handle each term according to unique rules. But
if you discover you're a talented programmer, and you further find that
having a macro that handles every single term correctly would be useful
over time, bear in mind that it *can* be done but developing it would
kill your chances of getting the project out the door on time, this
time, even if it might expedite future projects, if there are one.
>

OK, ready? Got a backup copy of the spreadsheet?

Record a vba macro following my recipe below. It will process one
record at a time. It will assume the cursor is in the first cell to be
processed by reversing word order, it will reverse the words and insert
whatever punctuation and spacing you want, then it will go to the next
cell in the same column, where you (the user) will have to decide
whether to run it or manually click the cursor into the next cell to
process.

Ready? Wait--iirc, you have to enable VBA in Excel, look into this if
you can't find how to record a macro:


Put the cursor at the very beginning of the first cell containing words
to be reversed.

Start recording

Select the cell (isn't this done with edit>select>cell? I forget...)

Search (edit>find) the selection for the character that separates the
words. Is it a space? If so, you'd search for the first space. I think
you can do this several ways (isn't there a symbolic way, like
edit>find>^s ?), but you'll have to research that if you need it.

Space selected? Fine, hit <delete> to kill the space, hold down <shift>
+ <right cursor> until the 2nd word is all selected, hit <shift> + <del>
to kill second word, copying it to the clipboard. Real power users will
know the right keyboard combos to select the next word, cut, paste, etc.

With one word left in cell, move cursor to beginning of cell, type what
you want to appear between words (punctuation, spacing, etc), move to
beginning of cell again and hit <shift><insert> to paste the second word.

Cursor down one cell, then put cursor at beginning of cell.

Stop recording.

Test and debug. Realize that after 8 or 10 test runs, you'll forget and
run the macro with the cursor in the wrong cell. Try to be more careful.

Heave ho,

Ned Bedinger
doc -at- edwordsmith -dot- com
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Use Doc-To-Help's XML-based editor, Microsoft Word, or HTML and
produce desktop, Web, or print deliverables. Just write (or import)
and Doc-To-Help does the rest. Free trial: http://www.doctohelp.com


- Use this space to communicate with TECHWR-L readers -
- Contact admin -at- techwr-l -dot- com for more information -


---
You are currently subscribed to TECHWR-L as archive -at- web -dot- techwr-l -dot- com -dot-

To unsubscribe send a blank email to
techwr-l-unsubscribe -at- lists -dot- techwr-l -dot- com
or visit http://lists.techwr-l.com/mailman/options/techwr-l/archive%40web.techwr-l.com


To subscribe, send a blank email to techwr-l-join -at- lists -dot- techwr-l -dot- com

Send administrative questions to admin -at- techwr-l -dot- com -dot- Visit
http://www.techwr-l.com/ for more resources and info.

Please move off-topic discussions to the Chat list, at:
http://lists.techwr-l.com/mailman/listinfo/techwr-l-chat


Follow-Ups:

References:
Reversing word order in Excel 2007 cells: From: Ken Poshedly

Previous by Author: Re: File and Folder diff utility
Next by Author: Re: Quick way to rename file extensions?
Previous by Thread: RE: Reversing word order in Excel 2007 cells
Next by Thread: Brief Blog on Russian SEO and Localization


What this post helpful? Share it with friends and colleagues:

Sponsored Ads


Sponsored Ads