splitting wrapped text - whats my delimiter?!

[jgolvach]

i have some address data in excel that I need to split up into separate columns. Normally i would use the split and resize functions with " " or "," as delimiters (just depending on how the data was separated), but now i have no easily identifiable delimiter. My data looks like this:

in one cell i have:

John Doe
4859 Wherever Dr
City, State 77777

This text is wrapped and no matter how wide i make this cell, the data appears just like this... however, if i deselect the wrapped text button, the data appears as follows:

John Doe4859 Wherever DrCity, State 7777

Is there a delimiter identifying wrapped text? Or some other way to tell excell that i want it to split the data based on where it wraps the text?

Thanks!!!
JG

 

[VoG]

Assuming that you are using Text to Columns, select Other as the delimiter and in the box hold down ALT and type 010 on the numeric keypad.

 

[jgolvach]

Vog,

Thank you for responding.

I am using the .split and .replace functions in vba:

Dim x As Variant
With Sheets(3)
If .Cells(1, 2).Value = "contact" Then
x = Split(Cells(1, 3).Value, " ")
.Resize(, UBound(x) - LBound(x) + 1).Value = x
Else
.Cells(8, 1).Value = "crud"
End If
End With

I cant figure out what to put within the "" on the x=split line so that vba knows to split the cell into multiple columns everytime excel wraps the text to the next line.

 

[jgolvach]

ha, ignore the "crud" part... that was just so i knew if there was a certain error when i was testing my code.

 

[VoG]

Try

x = Split(Cells(1, 3).Value, Chr(10))