counting characters to 50 and adding to new line

[Bill Blackburn]

Hi

Hoping someone can help. I need to be able to paste information into excel and then get excel to read the characters to 50 and then move the other info to another line and do the same command again until it has read all the data and put it in lines of 50.

 

[jbeaucaire]

Try this, I set the string in cell A1:

Sub Split50()
Dim MyVal As String, i As Long
MyVal = Range("A1")

    Do
        i = i + 1
        If Len(MyVal) > 50 Then
            Range("A" & i) = Left(MyVal, 50)
            MyVal = Right(MyVal, Len(MyVal) - 50)
        Else
            Range("A" & i) = MyVal
            Exit Do
        End If
    Loop
End Sub

 

[Bill Blackburn]

Thank you for that. But being new to Excel, where do I put this information for it to work - eg in a macro or just type it in a cell.

 

[jbeaucaire]

How to use the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The macro is installed and ready to use.

Put your long text string into cell A1.
Press Alt-F8 and select "Split50" from the macro list.

 

[Bill Blackburn]

Hi, I have just tried this and I can not get it to work - this is an example of the info that I am trying to count to 50 or less.

Hope this makes sense.
<TABLE style="WIDTH: 389pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=519 border=0><COLGROUP><COL style="WIDTH: 389pt; mso-width-source: userset; mso-width-alt: 18980" width=519><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 389pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=519 height=20><FORM name=frm_prods onsubmit="return _CF_checkfrm_prods(this)" action=incompleteOrders.cfm method=post>PI DATE: MAY 2005</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>PRODUCT DESCRIPTION: USB TYPE CONNECTORS</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>LASER 4 to 1 monitor, keyboard and mouse switch (USB)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>CONSOLE SIDE: 2 x 6 pin MD female (PS/2) & 1 x 15 pin HD female (VGA, SVGA)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>PC SIDE: 4 x USB B type and 4 x 15 pin HD male (VGA, SVGA)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>ALL CABLES INCLUDED: 4 sets of 1.8m 3*in*1 coaxial cables</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>BENEFITS:</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>* Provides easy access to 4 computers from 1 keyboard,video monitor, mouse</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>* LED indication for easy working status</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>FEATURES:</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>* Supports high video resolution, up to 1920 x 1440 with bandwidth of 250MH</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>* Plug and play</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>* Easy installation * no software required</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>* Hot key or push button for PC selection</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>* Auto scan for monitoring PC periodically</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>* Beeps when switching port confirmed</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>* Supports PS/2 mouse, PS/2 keyboard</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>* No need for external power </FORM></TD></TR></TBODY></TABLE>
cheers

 

[Bill Blackburn]

Hi, I have just tried this and I can not get it to work - this is an example of the info that I am trying to count to 50 or less.

Hope this makes sense.
<TABLE style="WIDTH: 389pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=519 border=0><COLGROUP><COL style="WIDTH: 389pt; mso-width-source: userset; mso-width-alt: 18980" width=519><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 389pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=519 height=20><FORM name=frm_prods onsubmit="return _CF_checkfrm_prods(this)" action=incompleteOrders.cfm method=post>PI DATE: MAY 2005</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>PRODUCT DESCRIPTION: USB TYPE CONNECTORS</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>LASER 4 to 1 monitor, keyboard and mouse switch (USB)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>CONSOLE SIDE: 2 x 6 pin MD female (PS/2) & 1 x 15 pin HD female (VGA, SVGA)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>PC SIDE: 4 x USB B type and 4 x 15 pin HD male (VGA, SVGA)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>ALL CABLES INCLUDED: 4 sets of 1.8m 3*in*1 coaxial cables</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>BENEFITS:</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>* Provides easy access to 4 computers from 1 keyboard,video monitor, mouse</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>* LED indication for easy working status</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>FEATURES:</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>* Supports high video resolution, up to 1920 x 1440 with bandwidth of 250MH</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>* Plug and play</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>* Easy installation * no software required</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>* Hot key or push button for PC selection</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>* Auto scan for monitoring PC periodically</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>* Beeps when switching port confirmed</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>* Supports PS/2 mouse, PS/2 keyboard</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>* No need for external power </FORM></TD></TR></TBODY></TABLE>
cheers

After thinking about it in more detail, what i need it to do is put all the info into one cell and then count the 50 characters and move the rest to the next cell and count 50 characters etc

thank you

 

[jbeaucaire]

The macro was designed to do exactly what you asked. Is all that sample text above in a A1 like I said before you activate the macro?

When you run the macro, what happens exactly?

 

[jbeaucaire]

Also, here's another version that will break your product description out into separate lines at the natural existing line feeds, then it will put any resulting lines with more than 50 characaters onto separate rows.

Again, the text to be evaluated it expected to be in cell A1, this time the macro puts the new text in column B.

Sub Split50b()
Dim MyArr, i, x, MyVal
MyArr = Split(Range("A1"), Chr(10))

    For i = 0 To UBound(MyArr)
        x = x + 1
        MyVal = MyArr(i)
        Do
            If Len(MyVal) <= 50 Then
                Range("B" & x).Value = MyVal
                Exit Do
            Else
                Range("B" & x).Value = Left(MyVal, 50)
                MyVal = Right(MyVal, Len(MyVal) - 50)
                x = x + 1
            End If
        Loop
    Next i

End Sub

 

[Bill Blackburn]

thank you for all your help. No, looking at the way I get the info, it does not all come in to cell a1. so is there a possibility that there is a way to get all the data into cell a1.

 

[jbeaucaire]

thank you for all your help. No, looking at the way I get the info, it does not all come in to cell a1. so is there a possibility that there is a way to get all the data into cell a1.

I don't know. Is there? You're the one dealing with your data. If you have access to the strings outside of Excel, you can copy it, switch to A1, press F2 and paste it in. Then run the macro.

That's all I can think of. You'll have to manage the incoming data, or redesign your whole question to more completely demonstrate the data, the problem and exact results.