Mobile menu

Copying the first word of a phrase into a new column (Word or Excel)
Thread poster: Charlie Bavington
Charlie Bavington  Identity Verified
Local time: 18:53
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: 19:53
Member (2003)
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: 18:53
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






SDL MultiTerm 2017
Guarantee a unified, consistent and high-quality translation with terminology software by the industry leaders.

SDL MultiTerm 2017 allows translators to create one central location to store and manage multilingual terminology, and with SDL MultiTerm Extract 2017 you can automatically create term lists from your existing documentation to save time.

More info »
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 »



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