Split string based off character count with other data

cboggie

New Member
Joined
Jan 26, 2014
Messages
6
Hey guys,

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

EquipmentNameIDLineNotes
1000333832H.P. : 14.01

RPM : 2100

Serial Number : 243526 KD

Manufacturer : DODGE

Model : SCXT325A
1000333833CFM : 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:

EquipmentNameIDLineNotes
10003338321H.P. : 14.01 RPM : 2100 Serial Number : 243526 KD Manufacturer :
10003338322: DODGE Model : SCXT325A
10003338331CFM : 3000 RPM Damper (Yes/No) : Yes H.P. : 75 Manufacturer :
10003338332: Phelps Model : CPW 270/Serial# 93174 Outlet Dimensions : 36" Comments
10003338333ts : 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:

1574107861523.png


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






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
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

cboggie

New Member
Joined
Jan 26, 2014
Messages
6
Eww, that didn't paste right with the cell alignments. let me know if this is confusing.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,017
Messages
5,599,362
Members
414,306
Latest member
Dennis_vdw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top