Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have an excel table with the following formatting:

1|     Line 1    |     Line 2     |    Line 3       |   Line 4        |
2|123 Main Street|   Apartment 1  |  NY, NY 10000   |                 |
3| 17 1st Street |  LA, CA 55555  |                 |                 |
4|  570 Comstock |      Suite 3   |Houston, TX 23123|                 |
5|Human Resources|   Building 23  |  Company Lane   |Madison, WI 21311|

I'm looking for a way to "right justify" or shift everything across the cells so it looks like this:

1|     Line 1    |     Line 2     |    Line 3       |   Line 4        |
2|               | 123 Main Street|   Apartment 1   |  NY, NY 10000   |
3|               |                |   17 1st Street |  LA, CA 55555   |
4|               |  570 Comstock  |      Suite 3    |Houston, TX 23123|
5|Human Resources|   Building 23  |  Company Lane   |Madison, WI 21311|

Each row has 1-4 columns of data that starts in Line 1. Does anyone know of a way to do this?

share|improve this question

3 Answers 3

up vote 1 down vote accepted

If the order of the addresses (that's what I think those are) is important, I would first insert a column on the extreme left side and put a sequence of numbers down the column. This makes 5 columns (1 'index' column, and 4 columns containing the parts of the addresses).

1. Sort by Line 4 to get all the spaces together, like this:

1| |     Line 1    |     Line 2     |    Line 3       |   Line 4        |
5|4|Human Resources|   Building 23  |  Company Lane   |Madison, WI 21311|
2|1|123 Main Street|   Apartment 1  |  NY, NY 10000   |                 |
3|2| 17 1st Street |  LA, CA 55555  |                 |                 |
4|3|  570 Comstock |      Suite 3   |Houston, TX 23123|                 |

[Blanks will get at the bottom]

2. Insert cells before column Line 1 where the cells in Line 4 column are empty (that is, for row numbers 3 to 5), so it now becomes like this:

1| |     Line 1    |     Line 2     |    Line 3       |   Line 4        |
2|4|Human Resources|   Building 23  |  Company Lane   |Madison, WI 21311|
3|1|               | 123 Main Street|    Apartment 1  |  NY, NY 10000   |
4|2|               |  17 1st Street |   LA, CA 55555  |                 |
5|3|               |  570 Comstock  |       Suite 3   |Houston, TX23123 |

3. Sort again by Line 4 so you get something like:

1| |     Line 1    |     Line 2     |    Line 3       |   Line 4        |
2|3|               |  570 Comstock  |       Suite 3   |Houston, TX23123 |
3|4|Human Resources|   Building 23  |  Company Lane   |Madison, WI 21311|
4|1|               | 123 Main Street|    Apartment 1  |  NY, NY 10000   |
5|2|               |  17 1st Street |   LA, CA 55555  |                 |

4. Insert again cells before column Line 1 where the cells in Line 4 are empty. (Now, only row number 5).

1| |     Line 1    |     Line 2     |    Line 3       |   Line 4        |
2|3|               |  570 Comstock  |       Suite 3   |Houston, TX23123 |
3|4|Human Resources|   Building 23  |  Company Lane   |Madison, WI 21311|
4|1|               | 123 Main Street|    Apartment 1  |  NY, NY 10000   |
5|2|               |                |  17 1st Street  |   LA, CA 55555  |

5. Now that everything's where it's supposed to be, sort the first column, the index we inserted at the very start, which you can delete after sorting.\

1| |     Line 1    |     Line 2     |    Line 3       |   Line 4        |
2|1|               | 123 Main Street|    Apartment 1  |  NY, NY 10000   |
3|2|               |                |  17 1st Street  |   LA, CA 55555  |
4|3|               |  570 Comstock  |       Suite 3   |Houston, TX23123 |
5|4|Human Resources|   Building 23  |  Company Lane   |Madison, WI 21311|

This doesn't require any formula, since you can get lost sometimes with the brackets and various nested ifs, and with a few columns, I believe that's actually faster to do! And more efficient than inserting cells one by one at each row.

If the order is not important, just do steps 1 to 4.

share|improve this answer
    
We could say it is an extension of Brad Patton's answer. –  Jerry Apr 25 '13 at 17:08
    
This is a very simple solution for a low number of columns, which this situation was. Thanks! –  ml9992 Apr 26 '13 at 15:30
    
You're welcome! :) –  Jerry Apr 26 '13 at 15:39

To shift the cells over select where you want the new cells insert and right-click. From the pop-up menu select Insert. Select Shift cells right to move them to the right.

enter image description here

To justify the text highlight the cells and use the Align Text Right button in the Alignment section.

enter image description here

share|improve this answer
    
I don't mean justify within the cells. I need to shift them across multiple cells. Let's say one row has only two columns of data (like row 3 does). Instead of Line 3 and Line 4 being blank, I need the data shifted over to those lines. Line 1 and 2 would then become blank. –  ml9992 Apr 25 '13 at 15:49
    
Sorry I keyed on the justify part of your question. Updated to handle shifting the cells. –  Brad Patton Apr 25 '13 at 15:51
    
That works row by row. I have 10000 rows, each with 1-4 columns of data. –  ml9992 Apr 25 '13 at 16:05
    
You can select multiple cells and shift them over. I don't know of any other way to selectively move cells. –  Brad Patton Apr 25 '13 at 16:12

Messy, but you could achieve it by creating 4 columns next to existing ones and using formulas.

Say Line 1 --> Line 4 is A:D then new columns are E:F

E

=IF(ISTEXT($D2),A2,"")

F

=IF(ISTEXT($D2),B2,IF(ISTEXT($C2),A2,IF(ISTEXT($B2),"")))

G

=IF(ISTEXT($D2),C2,IF(ISTEXT($C2),B2,IF(ISTEXT($B2),A2,"")))

H

=IF(ISTEXT($D2),D2,IF(ISTEXT($C2),C2,IF(ISTEXT($B2),B2,IF(ISTEXT($A2),A2,""))))
share|improve this answer
    
Thanks, this also works - and would be quicker to implement if you had a lot of columns. –  ml9992 Apr 26 '13 at 15:34

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.