Copying the first word of a phrase into a new column (Word or Excel)
Thread poster: Charlie Bavington
Charlie Bavington  Identity Verified
Local time: 08:07
French to English
Jan 9, 2006

[Word & Excel 2002; Win XP]

I have some glossary files consisting of 2 columns, the first column being a source word or phrase, the second being the target translation. For reasons I won't bore you with here, I would like to create a third column containing only the first word of each source phrase (which, of course, since some entries are one word only, may simply consist of that single word, i.e. there is no guarantee that the source phrases are >1 word in length). Note that this operation is to COPY the first word (not CUT), i.e. I wish the first word to remain in the source phrase.

I am happy to carry out this manipulation in either Word or Excel, but would like to automate it as much as is possible.
The two options which seemed most likely were:
a) in Excel, a formula which would take the first word from a cell and copy it to a new cell
b) in Word, a macro (recording keystrokes, rather than VB perhaps?) using Find to locate the first word in a cell in a table and copy it to another cell in the same row. This would be repetitive but still better than doing the whole thing manually.

However, I can't find either a suitable formula in Excel, or a suitable "Find" command in Word which will effectively just copy the first word in a string.

Has anybody got any bright ideas?
Thanks in advance,
Charlie


Direct link Reply with quote
 

Hynek Palatin  Identity Verified
Czech Republic
Local time: 09:07
English to Czech
+ ...
Copying the first word of a phrase into a new column (Word or Excel) Jan 10, 2006

In Word:

First, I would add the third (empty) column and convert the table to text, just to make things easier.

The macro would be:

Home
Ctrl-Shift-Right (to select the first word)
Ctrl+C (to copy it)
End
Ctrl+V
Down

You could edit the macro to add a loop that would run through the whole document, so that you don't have to run the macro manually for every line.

Also, some words in the third column will contain trialing spaces. You can remove them with one search and replace operation (replace " ^p" with "^p").


In Excel:

Even easier, you can extract the first word with the following formula. I hope I got the English function names right:

=IF(ISERROR(FIND(" ";A1));A1;LEFT(A1;FIND(" ";A1)-1))

A1 is the cell containing the source term.


Direct link Reply with quote
 
Charlie Bavington  Identity Verified
Local time: 08:07
French to English
TOPIC STARTER
Brilliant! Jan 10, 2006

The Excel method works just fine.
You were right about the English function names, although I had to change the ";" into ",", as follows:

=IF(ISERROR(FIND(" ",A3)),A3,LEFT(A3,FIND(" ",A3)-1))

Thank you very much for your help - you have saved me literally hours of work (I did one manually before posting the question, so I know how long it takes!). I'm very grateful.


Direct link Reply with quote
 


To report site rules violations or get help, contact a site moderator:


You can also contact site staff by submitting a support request »

Copying the first word of a phrase into a new column (Word or Excel)

Advanced search






Déjà Vu X3
Try it, Love it

Find out why Déjà Vu is today the most flexible, customizable and user-friendly tool on the market. See the brand new features in action: *Completely redesigned user interface *Live Preview *Inline spell checking *Inline

More info »
memoQ translator pro
Kilgray's memoQ is the world's fastest developing integrated localization & translation environment rendering you more productive and efficient.

With our advanced file filters, unlimited language and advanced file support, memoQ translator pro has been designed for translators and reviewers who work on their own, with other translators or in team-based translation projects.

More info »



All of ProZ.com
  • All of ProZ.com
  • Term search
  • Jobs