CODEWITHSUNDEEP
excel
R Castelvecchi
R Castelvecchi

Reputation: 81

How to align cells to the right

I have a set of data that I copy from a .pdf and paste into Excel. What I need it to do is align the cells to the right after I paste it.

Is there a way I can get Excel to align it all for me?

Example: Before

OR  MARKER  LE  20.92   20.92   104.6
CAC HOT SIDE    29.02   29.02   29.02
44.55   44.55   44.55           
16.77   16.77   33.54           
SENDER  25.21   25.21   25.21       
RH  35.72   35.72   35.72       
BRUSHLESS   310.2   279.18  279.18      
EXTENDER    R   101.6   101.6   101.6   
STRUT   288.21  288.21  288.21

After

OR  MARKER  LE  20.92   20.92   104.6
CAC HOT SIDE    29.02   29.02   29.02
                44.55   44.55   44.55
                16.77   16.77   33.54
      SENDER    25.21   25.21   25.21
          RH    35.72   35.72   35.72
    BRUSHLESS   310.2   279.18  279.18
EXTENDER    R   101.6   101.6   101.6
       STRUT    288.21  288.21  288.21

I am not aligning the text within the cells, but the cells themselves to the right. So the all the empty cells are to the left.

Upvotes: 2

Views: 1184

Answers (2)

nvuono
nvuono

Reputation: 3363

Sounds like people didn't understand your question and this is a tough thing to search for--moving actual cell values to the right based on the rightmost cell with values in it. I encounter this after having to delimit text rows based on spaces when the text wasn't designed to include real delimiters.

It doesn't appear that there is any kind of built-in function to do what you're asking so I had to build myself a VBA macro.

You can change startRow and startCol as needed and then tweak things for your exact case but it looks like the following:

Sub RightShiftColumnValues()
    Dim startCol As Integer
    Dim startRow As Integer
    Dim endCol As Integer
    Dim numRows As Integer

    ' set starting row and column
    startRow = 1
    startCol = 1

    ' find number of rows
    Cells(startRow, startCol).Select
    Selection.End(xlDown).Select
    numRows = Selection.Row - startRow + 1
    ' find rightmost column with a value
    Dim i As Integer
    For i = startCol + 1 To 500
    Cells(startRow, i).Select
        Selection.End(xlDown).Select
        If (Selection.Row > numRows + startRow) Then
            endCol = i - 1
            Exit For
        End If
    Next i

    Dim currRow As Integer
    ' now go through each row and right align the actual cells
    For currRow = startRow To numRows
        Dim j As Integer
        Dim rowBlanks As Integer
        rowBlanks = 0 ' track number of blank columns in this row starting from the right
        ' Cells(currRow, endCol).Select ' select current cell if you want to uncomment and watch the process
        For j = 1 To endCol - 1
            If (IsEmpty(Cells(currRow, endCol - j).Value)) Then
                    rowBlanks = rowBlanks + 1
            Else
                ' shift values
                Cells(currRow, endCol - j).Select
                Cells(currRow, endCol - j + 2 + rowBlanks).Value = Cells(currRow, endCol - j).Value
                Cells(currRow, endCol - j).Value = ""
            End If
        Next j
    Next currRow
End Sub

Upvotes: 3

Shawn Jamison
Shawn Jamison

Reputation: 45

Numbers should already align to the right.

for the text to align you can select the cells and right click to get the context menu. left click on Format cells. click on the Alignment tab set your Horizontal to "Right Indent" Click Ok

Another way is to select the text then click on the Right Justify Icon in the ribbon.

Upvotes: 0

Related Questions