[RESOLVED] Loop in a Cell - Wrapped Cell

[capk]

Hey Guys,

I'm writing a VBA Macro which splits a long String in a lot of substrings and copies them in new Columns.
My problem now is, that there is a Column called "Spare Parts" and in that Column there are only Cells which contain wrapped text.



The lines in the cells, except of the first 3, always look the same:
---> Spare Part ID(10Digits) , Spare Part Name , Number of Units , Price per Unit

And I need to split those 4 parts into individual strings.
I already managed to split those 4 parts and copy them in new Columns if there is only one String in a cell.(=No wrapped cell)

But I dont know how to do that in a Wrapped Cell?
I think I need to get the index of the position of the first "line break" character and save that value and then work with that ?

Somebody has any ideas and maybe some code examples or something like that?

Thank you guys in advance!

[westconn1]

can you post a workbook (zip first) with a sample of the data

probably split the cell on chr(10) (but hard to guess without some sample of the data), then loop the array and split each element to put the value into individual cells

[capk]

Here is a example with a sample of data.

TestData.zip

[vbfbryce]

As Pete says, you can split that long string by looking for the ascii code 10, like:

myNumber=asc(myChar)

where myChar is the value of a single character when looping through the string.

[capk]

As both of you guys said, I did the loop with Chr(10)

srcRow = 1
dstRow = 1
NumRows = Range("A2", Range("A2").End(xlDown)).Rows.Count

For x2 = 1 To NumRows
    srcRow = srcRow + 1
    dstRow = dstRow + 1
    strLen = Len(Cells(srcRow, "B"))
    
If strLen <> 0 Then
test = Replace(Cells(srcRow, "B"), Chr(10), "", 1, -1, vbTextCompare)
x = Len(test)
anzLeer = strLen - x 
rightLine = Cells(srcRow, "B").Value

    For o = 0 To anzLeer
    
        newLineLen = InStr(rightLine, Chr(10))
        leftLine = Left(rightLine, newLineLen)
        '#########SPLIT#########
        rightLine = Replace(rightLine, leftLine, "")

            If o = anzLeer Then
                leftLine = rightLine
            End If

    MsgBox (leftLine)
    Next
End If
Next
End Sub

If you insert this in my previous uploaded sample, it will return every line in all cells one by one as I wanted.
Now I only need to implement the rest of the code to split these lines i get returned.
But thats the lesser Problem thank you guys for helping