Mobile menu

How to swap text in Excel-colums
Thread poster: Heinrich Pesch

Heinrich Pesch  Identity Verified
Finland
Local time: 02:06
Member (2003)
Finnish to German
+ ...
Sep 28, 2007

I have a glossar with source and target colums. Part of the target cells are empty, these I should translate. So I would like to write a formula for the column C, that looks, if the cell in column B is empty and copies the content from column A, otherwise it copies the content of cell B.
But I seem to have forgotten the syntax. I tried
=IF(A1="";A1;B1)
But that copies content of column A to all cells.
Help!
Regards
Heinrich


Direct link Reply with quote
 

Cecilia Falk  Identity Verified
Local time: 01:06
English to Swedish
Try this Sep 28, 2007

=IF(B1="";(A1);B1)

Best regards,
Cecilia


Direct link Reply with quote
 

Heinrich Pesch  Identity Verified
Finland
Local time: 02:06
Member (2003)
Finnish to German
+ ...
TOPIC STARTER
Thanks, but i can't make it work Sep 28, 2007

=IF(A2="";(B2);A2)
does the same as
=IF(A2="";B2;A2)
it copies the content from A though there is text in B.
More ideas?


Direct link Reply with quote
 

Cecilia Falk  Identity Verified
Local time: 01:06
English to Swedish
B1 not A1 Sep 28, 2007

Hello again, Henirich,
It should say;

=IF(B1="";(A1);B1)

not
=IF(A1="";(A1);B1)

This works for me. BTW, thanks for this idea, never thought of it myself, but I will find plenty of use for it.

Best regards,
Cecilia


Direct link Reply with quote
 

Heinrich Pesch  Identity Verified
Finland
Local time: 02:06
Member (2003)
Finnish to German
+ ...
TOPIC STARTER
Of course Sep 28, 2007

How stupid of me. Thanks a lot, Cecilia.
Heinrich

But how can I achieve, that excel copies also the formatting? I thouhgt I could define fontcolor red to the A-column, so I would see, which item comes from A and which from B. Then I could define the red stuff as nontranslatable.
Can it be done?


[Bearbeitet am 2007-09-28 08:59]


Direct link Reply with quote
 

Cecilia Falk  Identity Verified
Local time: 01:06
English to Swedish
Not stupid ;-) Sep 28, 2007

I make these kind of mistakes EVERY time I create macros, and once you spot the error you can't understand how you could have missed it...

Have a nice weekend!
Cecilia


Direct link Reply with quote
 

Cecilia Falk  Identity Verified
Local time: 01:06
English to Swedish
Conditional Formatting Sep 28, 2007

Hi again,

You could do this by using Conditional Formatting:

Select Cell C1.
Choose Format>Conditional Formatting.

Set the following condition:
cell value: Does not Equal

Then click on Cell A1.

It now says: =$A$1
Delete the two $:
So it says: =A1

Click Format and choose for example red as the color.
Click OK and OK again.

Then drag the formatting down through the column. Now all cells that are already translated will show up in red in Column C.

Best regards,
Cecilia


[Edited at 2007-09-28 09:49]

[Edited at 2007-09-28 09:54]

[Edited at 2007-09-28 11:49]


Direct link Reply with quote
 

Heinrich Pesch  Identity Verified
Finland
Local time: 02:06
Member (2003)
Finnish to German
+ ...
TOPIC STARTER
Thank your very much! Sep 28, 2007

This is very useful.
Regards
Heinrich


Direct link Reply with quote
 

Claudia Krysztofiak  Identity Verified
Germany
Local time: 01:06
English to German
+ ...
Great idea, thank you both! Sep 28, 2007

I have only used excel formulas for calculation so far.
This is just great! It will be very helpful.

Heinrich and Cecilia, thanks for asking and answering!


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 »

How to swap text in Excel-colums

Advanced search






WordFinder
The words you want Anywhere, Anytime

WordFinder is the market's fastest and easiest way of finding the right word, term, translation or synonym in one or more dictionaries. In our assortment you can choose among more than 120 dictionaries in 15 languages from leading publishers.

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