Mobile menu

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

Heinrich Pesch  Identity Verified
Finland
Local time: 17:36
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: 16:36
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: 17:36
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: 16:36
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: 17:36
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: 16:36
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: 16:36
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: 17:36
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: 16:36
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






PerfectIt consistency checker
Faster Checking, Greater Accuracy

PerfectIt helps deliver error-free documents. It improves consistency, ensures quality and helps to enforce style guides. It’s a powerful tool for pro users, and comes with the assurance of a 30-day money back guarantee.

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



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