Split string based off character count with other data

[cboggie]

Hey guys,

I have some data that contains "Long Text" as well as other data.

Equipment

Name

ID

Line

Notes

1000333832				H.P. : 14.01 RPM : 2100 Serial Number : 243526 KD Manufacturer : DODGE Model : SCXT325A
1000333833				CFM : 3000 RPM Damper (Yes/No) : Yes H.P. : 75 Manufacturer : Phelps Model : CPW 270/Serial# 93174 Outlet Dimensions : 36" Comments : Model CPW270

In excel, if i take off word wrap, it combines everything into one line, and looks perfect. However, as you can see, when i paste it, it looks to be adding a carriage return.

This is what it should look like when i run my code:

Equipment

Name

ID

Line

Notes

1000333832			1	H.P. : 14.01 RPM : 2100 Serial Number : 243526 KD Manufacturer :
1000333832			2	: DODGE Model : SCXT325A

1000333833			1	CFM : 3000 RPM Damper (Yes/No) : Yes H.P. : 75 Manufacturer :
1000333833			2	: Phelps Model : CPW 270/Serial# 93174 Outlet Dimensions : 36" Comments
1000333833			3	ts : Model CPW270

But it doesn't due to (what i believe to be) a carriage return or something weird.

Although it looks fine in the cell:

It looks different (with carriage) returns in the excel data box

Here is my code. The data starts in A1 with A1-E1 being headers. Once the code is executed, it puts the results starting in F1

Sub test()
Dim txt As String, temp As String, colA As String, colB As String, colC As String, ColD As String, ColE As String, ColF As String
Dim a, b() As String, n, i As Long
Dim Counter As Integer
Const myLen As Long = 75
a = Range("a1").CurrentRegion.Value
ReDim b(1 To Rows.Count, 1 To 5)
    For i = 1 To UBound(a, 1)
        If a(i, 1) <> "" Then
            colA = a(i, 1)
            colB = a(i, 2)
            colC = a(i, 3)
            ColD = Counter
            txt = Trim(a(i, 5))
                Do While Len(txt)
                    If Len(txt) <= myLen Then
                            temp = txt
                        Else
                            temp = Left$(txt, InStrRev(txt, " ", myLen))
                            temp = Replace(temp, vbLf, "")
                    End If
                    If temp = "" Then Exit Do
                    n = n + 1
                    b(n, 1) = colA: b(n, 2) = colB: b(n, 3) = colC: b(n, 4) = Counter
                    'b(n, 5) = Replace(temp, vbLf, "")
                    b(n, 5) = Trim(temp)
                    txt = Trim(Mid$(txt, Len(temp) + 1))
                    Counter = Counter + 1
                Loop
            Counter = 1
        End If
    Next
Range("f1").Resize(n, 5).Value = b
End Sub

 

[cboggie]

Eww, that didn't paste right with the cell alignments. let me know if this is confusing.