Splitting cells by non-printable characters

Morning All,

I've had a quick look at past posts and there is plenty on splitting cells but I haven't been able to find anything to help me with the following:

I've pulled address details from a database which have populated into a single field with each line separated by 2 'square' characters. I need to split each line into a separate cell for a mail merge. I've seen these characters before and always assumed they represent a space or 'return' and I believe is an ASCII character?

If I select a cursor in the cell the lines drop as they should with 1 square character left at the end of each line and it stays this way when I leave the cell.

I've tried CLEAN & TRIM functions; the first removes the characters, the second doesn't recognise the character at all. The most promising seems the 'Text to Columns' function, setting it as a delimited string, but I don't seem to be able to enter this particular character as the delimiter under the 'other' option.

Any ideas?

Many thanks in advance,

Simon.

Try this:

Sub SplitByCharacter()
Columns("A:A").SpecialCells(xlCellTypeConstants, 2).TextToColumns Destination:=Range("A1"), _
    DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Other:=True, OtherChar:=Chr(10)
End Sub

Ok, simpler version.

Sub SplitByCharacter()
Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Other:=True, OtherChar:=Chr(10)
End Sub

Simon, Text To Columns only works on one column of data at a time. You could make this process so much easier by uploading a workbook with this data in it so we can look at it concurrently.

Try this instead:

Sub SplitByCharRowByRow()
'JBeaucaire  (10/13/2009)
Dim MyArr, i As Long, c As Long, LR As Long
Application.ScreenUpdating = False
LR = Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To LR
    MyArr = Split(Cells(i, "A"), Chr(10))
    For c = 0 To UBound(MyArr)
        Cells(i, 1 + c) = WorksheetFunction.Clean(MyArr(c))
    Next c
Next i

Cells.WrapText = False
Cells.Columns.AutoFit
Application.ScreenUpdating = True
End Sub