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

Heinrich Pesch  Identity Verified
Finland
Local time: 20:32
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: 19:32
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: 20:32
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: 19:32
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: 20:32
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: 19:32
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: 19:32
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: 20:32
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: 19:32
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






Anycount & Translation Office 3000
Translation Office 3000

Translation Office 3000 is an advanced accounting tool for freelance translators and small agencies. TO3000 easily and seamlessly integrates with the business life of professional freelance translators.

More info »
BaccS – Business Accounting Software
Modern desktop project management for freelance translators

BaccS makes it easy for translators to manage their projects, schedule tasks, create invoices, and view highly customizable reports. User-friendly, ProZ.com integration, community-driven development – a few reasons BaccS is trusted by translators!

More info »



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