The following macro works on the two examples you've given. You must select the cells you want it to work on before running the macro. I really suspect it won't work on real data for a variety of reasons:
Sub blah()
Set zzz = Selection
zzz.TextToColumns DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Space:=True
Set StartCll = zzz.Cells(1)
StartCllVal = StartCll.Value
For Each cll In zzz.Cells
Set CurrentCell = cll
With cll
' .Select
.HorizontalAlignment = xlRight
.VerticalAlignment = xlCenter
If .Value <> StartCllVal Then
Application.DisplayAlerts = False: Range(StartCll, .Offset(-1)).MergeCells = True: Application.DisplayAlerts = True
Set StartCll = cll
StartCllVal = StartCll.Value
End If
End With
Next cll
Application.DisplayAlerts = False: Range(StartCll, CurrentCell).MergeCells = True: Application.DisplayAlerts = True
End Sub
Supply a file with realistic data for a more refined solution.
Originally Posted by
luxor
then l can use ms access to create a new excel sheet as unmerged cells.
?!!
Forget how you think you want to get to your goal; what is your goal?