CODEWITHSUNDEEP
arraysvbaexcel
johndoe253
johndoe253

Reputation: 237

Whole string in cell not splitting

I'm running this code in vba and I would like the whole cell to get separated into an array in a new sheet but I cannot get it to split everything in the cell as pictured below. I would like for it to split in a new cell and a new array so I can search for key words in that array. Please take a look at the code and see what can be done.

Thanks

Sub SplitWithFormat()
    Dim R As Range, C As Range
    Dim i As Long, V As Variant


Set R = Range("d1", Cells(Rows.Count, "d").End(xlUp))
For Each C In R
    With C
        .TextToColumns Destination:=.Offset(0, 1), DataType:=xlDelimited, _
        consecutivedelimiter:=True, Tab:=False, semicolon:=True, comma:=False, _
        Space:=True, other:=False

        .Copy
        Range(.Offset(0, 1), Cells(.Row, Columns.Count).End(xlToLeft)).PasteSpecial xlPasteFormats
    End With
Next C
Application.CutCopyMode = False

End Sub​

enter image description here

Upvotes: 1

Views: 55

Answers (1)

dbmitch
dbmitch

Reputation: 5386

Can't tell from your example if your cell is wrapped with a CR-LF or just a LF.

Assuming it's just a line feed this should work:

Replace this line

    .TextToColumns Destination:=.Offset(0, 1), DataType:=xlDelimited, _
    consecutivedelimiter:=True, Tab:=False, semicolon:=True, comma:=False, _
    Space:=True, other:=False

with this line

.TextToColumns Destination:=.Offset(0, 1), DataType:=xlDelimited, _
consecutivedelimiter:=True, Tab:=False, semicolon:=True, comma:=False, _
Space:=True, other:=True, Otherchar:=vbLf

If it actually is a combination of - then use this:

.TextToColumns Destination:=.Offset(0, 1), DataType:=xlDelimited, _
consecutivedelimiter:=True, Tab:=False, semicolon:=True, comma:=False, _
Space:=True, other:=True, Otherchar:=vbCrLf

EDIT - convert split cells into array

Add new declarations at top

Dim varHorizArray As Variant Dim rge As Range Dim intCol As Integer

Before .Copy command, add

Set rge = Selection
varHorizArray = rge

Example of using resulting array, add to bottom

    ' Array returned is two dimensional - 1 row by 7 columns
    For intCol = LBound(varHorizArray, 2) To UBound(varHorizArray, 2)
Debug.Print varHorizArray(1, intCol)
    Next intCol

Upvotes: 1

Related Questions