counting characters to 50 and adding to new line

B

Bill Blackburn

New Member
Joined
Jul 6, 2009
Messages
5
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.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
Try this, I set the string in cell A1:
Code: 
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
 
Upvote 0
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.

:eeek:
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Bill Blackburn said:
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
Click to expand...

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
 
Upvote 0
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?
 
Upvote 0
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.
Code: 
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
 
Upvote 0
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.
 
Upvote 0
Bill Blackburn said:
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.
Click to expand...

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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Extracting data with VBA macro button from files in designated folder
2 3
Replies
20
Views
714
Micron
M
E
  • Question Question
Multiple questions for beginners
Replies
3
Views
308
excelnewlearner
E
C
  • Question Question
Macro run from Excel to transfer data in new Word doc won't output into Word
Replies
5
Views
357
cpu97
C
N
  • Question Question
Copy results into new sheet and new line. I need to do this like 1000 times.
Replies
3
Views
428
kevin9999
K
C
  • Question Question
New Line Data
2
Replies
11
Views
396
Jeffrey Mahoney
Jeffrey Mahoney

Forum statistics

Threads
1,223,248
Messages
6,171,021
Members
452,374
Latest member
keccles

Share this page

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
Back
Top