Aligning Cells when Importing from CSV

Marinos works with CSV files a lot. In his case, the CSV files are created by a custom application and he found that he can even include formulae in them. So if a line of the CSV file contains ",,,Total:,=SUM(D5:D13),,,," the formula is evaluated and all is fine. One thing Marinos wants to do, however, is indicate in the CSV file how individual cells should be justified after they are imported into Excel. He seems to remember that in Lotus 123 he could use a prefix character to indicate the alignment of the cell (' for left, ^ for middle, and " for right), and the same capability would be great in Excel?

There is no way to do this in Excel; alignment of imported data is based on system defaults, such that text is left-justified and numbers are right-justified. One option, however, would be to add a prefix character that you could then later "parse" with a macro to apply the desired alignment. For instance, you could use "<" for left, "^" for center, and ">" for right. When Excel imports the CSV files, the fields are treated as text. You can then run this macro to search for the leading alignment character and do the desired action:

Sub SetJustification()
  Dim rCell As Range
  For Each rCell In ActiveSheet.UsedRange
    With rCell
      Select Case Left(.Value, 1)
        Case "<"
          .Value = Mid(.Value, 2)
          .HorizontalAlignment = xlHAlignLeft
        Case "^"
          .Value = Mid(.Value, 2)
          .HorizontalAlignment = xlHAlignCenter
        Case ">"
          .Value = Mid(.Value, 2)
          .HorizontalAlignment = xlHAlignCenter
      End Select
    End With
  Next
  Set rCell = Nothing
End Sub

The macro checks each cell in the worksheet. If the cell begins with an alignment character, then the character is removed and the proper alignment is applied.