Mixing two columns

[etrader]

when combining two columns using C1=A1&B1 formula, the column C only works until the columns A and B exist; and the column C does not work when deleting A or B.

Can you suggest a solution to make an independent column from other columns ?

[ChemistB]

I'm not sure what you mean. How can you have a worksheet without a column A or B? Do you mean that A1 and B1 are empty? In any case, if you put into C1 the formula

Please Login or Register  to view this content.

it will always concatinate the values in A1 and B1. Please give an example.

[Paul]

Etrader,

That doesn't make too much sense to me. Can you post an example of the issue you're seeing?

If there's nothing in A or B, the formula in C will return an empty string, "", and show nothing in the cell. Works as intended.

If you delete the values in A or B, the formula in C still works.

If you delete the CELL (e.g. right-click on cell, choose Delete, shift Up) then yes, the formula will freak out because it's now trying to reference a non-existent cell (resulting in a #REF! error).

To avoid #REF! errors you could either not delete entire CELLS (just the values in them), or use a formula like:

=INDEX(A:A,ROW())&INDEX(B:B,ROW())

[etrader]

Paul, you got my problem; but your solution did not resolved. For example:

A B C
First Name Last Name Last Name, First Name

I want to mix columns A and B to create column C. After creating column C I do not need A and B anymore; thus I want to delete A and B. C will become A, as:

A
Last Name, First Name

[Paul]

Ok... after getting your results in column C, select column C and choose Edit -> Copy. Then choose Edit -> PasteSpecial -> Values. Column C will now be converted to static text instead of formulas. You can then delete columns A and B.

[etrader]

Perfect solution, Paul
Now it works perfectly