Splitting cells by non-printable characters

**JBeaucaire** · 10-12-2009, 06:42 AM

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

**Simon1185** · 10-12-2009, 08:30 AM

Thanks JBeaucaire,

I knew this day would come - I've not had to play with VBA since college!

I understand to a degree the code you've written; in that I can see you stipulate a range and the conditions of the split, but unfortunately I've never had to apply any VBA in Excel and am not sure how?!

I had the top cell of a blank row selected, opened VBA, pasted the data into the window of a new module (is this right?) and adjusted the range from A:A and destination range to suit, but when I clicked to run I got a run-time error '1004' stating that my command couldn't be run for multiple selections?

Thanks for your swift response.

Splitting cells by non-printable characters

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Re: Splitting cells by non-printable charaters

Re: Splitting cells by non-printable charaters

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions