Here's a different approach. It parses the data and puts it into the leftmost
and rightmost cells respectively. It assumes the number and date are in A1,
the number is followed by a space and the date, and this is the only text in
the cell. If otherwise, post a realistic sample.

Sub ParseNumberAndDate()
Dim sData As String
Dim iPos As Integer
Dim Wks As Worksheet

For Each Wks In ActiveWorkbook.Worksheets

'Get the data
sData = Wks.Range("A1").Value

'Find the position of the delimeter
iPos = InStr(1, sData, " ", vbTextCompare)

'Populate the leftmost cell of the report
With Wks.Cells(1, 1)
.HorizontalAlignment = xlLeft
.NumberFormat = "@"
.Value = Left$(sData, iPos - 1)
End With

'Populate the rightmost cell of the report
With Wks.Cells(1, 12)
.HorizontalAlignment = xlLeft
.NumberFormat = "@"
.Value = Mid$(sData, iPos + 1)
End With
Next Wks

End Sub

You should be able come up with something suitable from this.

Regards,
Garry

"ka2cil" wrote:

Garry, thx for your ideas. I'm just a recipient of the report files,
therefore, titles are already part of the text. In some cases, I can't left
justify the leftmost column and/or right justify the rightmost column.
However I think I can work off your ideas and try to come up with something.
If not, you might hear from me again :) Thx!

"GS" wrote:

Alternatively, if you want the data to display in cells on the worksheet:

Set the leftmost column to .HorizontalAlignment = xlLeft
Set the rightmost column to .HorizontalAlignment = xlRight

'Select the center columns of the report, then
Set the center columns to .HorizontalAlignment = xlCenterAcrossSelection

HTH
Garry