Technical forums » Office applications »
How to swap text in Excel-colums
Thread poster: Heinrich Pesch
Heinrich Pesch
Heinrich Pesch  Identity Verified
Finland
Local time: 09:11
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
 
Cecilia Falk
Cecilia Falk  Identity Verified
Local time: 08:11
English to Swedish
Try this Sep 28, 2007

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

Best regards,
Cecilia
 
Heinrich Pesch
Heinrich Pesch  Identity Verified
Finland
Local time: 09:11
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?
 
Cecilia Falk
Cecilia Falk  Identity Verified
Local time: 08:11
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
 
Heinrich Pesch
Heinrich Pesch  Identity Verified
Finland
Local time: 09:11
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]
 
Cecilia Falk
Cecilia Falk  Identity Verified
Local time: 08:11
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
 
Cecilia Falk
Cecilia Falk  Identity Verified
Local time: 08:11
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 ... See more
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] ▲ Collapse
 
Heinrich Pesch
Heinrich Pesch  Identity Verified
Finland
Local time: 09:11
Member (2003)
Finnish to German
+ ...
TOPIC STARTER
Thank your very much! Sep 28, 2007

This is very useful.
Regards
Heinrich
 
Claudia Krysztofiak
Claudia Krysztofiak  Identity Verified
Germany
Local time: 08:11
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!
 


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

Moderator(s) of this forum
Maya Gorgoshidze[Call to this topic]
Prachya Mruetusatorn[Call to this topic]

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

How to swap text in Excel-colums






Trados Business Manager Lite
Create customer quotes and invoices from within Trados Studio

Trados Business Manager Lite helps to simplify and speed up some of the daily tasks, such as invoicing and reporting, associated with running your freelance translation business.

More info »
Protemos translation business management system
Create your account in minutes, and start working! 3-month trial for agencies, and free for freelancers!

The system lets you keep client/vendor database, with contacts and rates, manage projects and assign jobs to vendors, issue invoices, track payments, store and manage project files, generate business reports on turnover profit per client/manager etc.

More info »