Should be able to use the text to column wizard with "Space" as delimiter.

Hi Rob, would you mind displaying in the earlier attached w/sheet

Thanks

Ian

Your sample file seems to have non-standard spaces, copy one and use that as the delimiter in the “Other” option

Sorry Rob, I don't follow. Is it possible to show me by way of the sample sheet I sent.

Thanks

Quite an exercise Rob, much harder than I thought.

I may rethink about the source data.

Thanks for your help

Ian

Hi,

the result you were looking for should look like this:

Is that right?

If yes, then please put this code snippet into a VBA module:

Option Explicit

Sub ConvertRows()
    Dim myConvertString As String, myNewString As String, myFinalString As String
    Dim mySplitter As Variant
    Dim i As Long, rowStart As Long, rowCount As Long
    Dim hasAppeared As Boolean
    Dim appearedCount As Integer
    Dim myWorksheet As Worksheet
    Set myWorksheet = Worksheets("Sheet1")
    appearedCount = 0
    hasAppeared = False
    For i = 1 To 1000000
        With myWorksheet
            If .Cells(i, 1) <> "" Then
                rowCount = rowCount + 1
            Else
                Exit For
            End If
        End With
    Next
    For rowStart = 1 To rowCount
        myFinalString = ""
        myNewString = ""
        With myWorksheet
            myConvertString = .Cells(rowStart, 1).Value
            For i = 1 To Len(myConvertString)
                If Asc(Mid(myConvertString, i, 1)) <> 160 Then
                    appearedCount = 0
                    myNewString = myNewString + Mid(myConvertString, i, 1)
                Else
                    If appearedCount = 0 Then
                        hasAppeared = True
                        If hasAppeared = True Then
                            myNewString = myNewString + ";"
                            appearedCount = appearedCount + 1
                        Else
                            appearedCount = 0
                        End If
                    End If
                End If
            Next
        End With
        mySplitter = Split(myNewString, ";")
        For i = 0 To 10
            On Error Resume Next
            Select Case i
                Case Is < 5
                    If mySplitter(i) <> "" Then myFinalString = myFinalString + mySplitter(i) + ";"
                Case Else
                    myFinalString = myFinalString + mySplitter(i - 1) & ";" + mySplitter(i)
            End Select
        Next
        mySplitter = Split(myFinalString, ";")
        With myWorksheet
            Select Case UBound(mySplitter, 1)
                Case 4
                    .Cells(rowStart, 3).Value = mySplitter(0)
                    .Cells(rowStart, 4).Value = mySplitter(2)
                    .Cells(rowStart, 5).Value = mySplitter(3)
                Case 5
                    .Cells(rowStart, 3).Value = mySplitter(0)
                    .Cells(rowStart, 4).Value = mySplitter(2)
                    .Cells(rowStart, 5).Value = mySplitter(3) & " " & mySplitter(5)
                Case Else
                    MsgBox "You have to adjust the strings!"
            End Select
            .Cells(1, 6).Value = "On left how Raisor suggests!"
        End With
    Next
End Sub

Select allOpen in new window

Best regards,

Raisor

Thanks Raisor, very kind of you.

I've just opened it and works a treat.

Very much appreciated

Ian