Split string based off character count with other data


New Member
Jan 26, 2014
Hey guys,

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

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:

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:


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
                            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
            Counter = 1
        End If
Range("f1").Resize(n, 5).Value = b
End Sub


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

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...