Hey guys,
I have some data that contains "Long Text" as well as other data.
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:
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
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
VBA Code:
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