Splitting up cell by #characters

Hi,

I am building a template to format text that will eventually go into a text field in a program, and I want to split up long strings so that if the cell contains > 100 characters, the first 100 characters will go on the first line, and the next 100 will go on the row below (will need to insert a row each time), and the next 100 will go on the row below that... for ex:

gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg gggggggggggggggggggggggggggggg (310 characters)

would end up like:

gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg gggggggggggggggggggggggggggggg (100 characters)
(new row) gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg gggggggggggggggggggggggggggggg (100 characters)
(new row)
gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg gggggggggggggggggggggggggggggg (100 characters)
(new row)
gggggggggg (10 characters)

This is a bit beyond my macro education, so I need some help. I found some code online and have a tiny bit of a start...

Sub lengths()
Dim var1 As String
Dim first As Integer

var1= "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"

first = Len(var1)

If ActiveCell > first Then

ActiveCell.EntireRow.Insert
Else
End If

End Sub

As you can probably tell, all this does is insert a row... but I was excited that it at least recognizes that the cell contains >100 characters. If anyone can help me out here I would REALLY appreciate it. I'm guessing this isn't TOO hard for someone who actually knows what they are doing... =).

Thanks in advance,

Rebecca

Hi Rebecca,

I'm not quite clear on your worksheet layout. Is there just a single column containing those strings? Or is there stuff in other columns that we have to worry about?

How would you like this to work, should the strings be split into separate cells when they are entered? Or do the sheets containing these strings already exist, so the best thing would be to find all of them at once? Or do you want to do it manually, by selecting a cell containing such a string and running the macro?

Thanks for clarifying. :)

Hi Cas,

Yes, in fact, all possibly long strings would be in column D, and then I would want the remainder characters (any over 100, and then for each 100) to be placed 1 row below (new row inserted) in column C. I tried to attach the file for you to see better, but I get "invalid file" when I hit upload - I have both an xls and xlt file I could send... but apparently neither is accepted. But hopefully you will understand what I am trying to do now.

Thanks again,

Rebecca

Hi Rebecca,

To upload an Excel file you can zip it first, although I think your description is pretty clear now. I think the key here is to keep it as simple as possible.

If this is a one-off and you were happy to parse the text into multiple columns instead of multiple rows, you could just use the text-to-columns feature (fixed width) and do the entire D column in one go? It's going to be much easier for you than trying to split the string with VBA, which is something I think you would find quite tricky.

I hope that helps,

Colin

That's a really good idea, Colin. :cool:

I was thinking about using a combination of Len() and Left() worksheet functions, but that would still require inserting rows via VBA, so it's not that much use.

Rebecca,

This example shows one fairly simply way to split a string into several smaller strings of a pre-determined size. You'll need to work out how to loop through your column and where/when to insert new rows, but post your code here if you get stuck. (hint: since you're using "activecell", take a look at "activecell.offset". You might also want to consider using "cells(row,column)" instead of "activecell". Both approaches permit using variables when describing a cell's location, which is essential for looping through a range of cells.)
Don't forget to use the F8 key to step through the code so you can see exactly what each line does.


Sub splitChars()

Dim strONE As String
Dim strTWO As String
Dim intA As Integer

strONE = Range("D1").Value
intA = 1
Do While Len(strONE) > 10
strTWO = Left(strONE, 10)
strONE = Right(strONE, Len(strONE) - 10)
Cells(intA, 1).Value = strTWO
intA = intA + 1
Loop
Cells(intA, 1).Value = strONE

End Sub

I'm curious about why you're pre-formatting the text rather than splitting it out at run-time. Are you preparing it for use with an existing program, or also creating/adapting the program it will be used with?

-geodekl

Also, you don't really need to build a comparison string to see whether your cell should be split:

if len(activecell.value) > 100 then
'insert your row or whatever
end if

This may do what you wish, rebecca:
Sub Rebecca()
Const iLen As Long = 100 ' change to suit
Const sCol As String = "D" ' change to suit

Dim iRow As Long ' row index
Dim cell As Excel.Range ' cell of interest
Dim sOut As String ' output string
' iPos partitions sOut | k * iLen characters at left, 1 to iLen at right
Dim iPos As Long

Application.ScreenUpdating = False
' loop backwards since we're inserting rows
For iRow = Cells(Rows.Count, sCol).End(xlUp).Row To 1 Step -1
With Cells(iRow, sCol)
sOut = .Text ' snag the cell text
.Value = Left(sOut, iLen) ' replace with the first bunch o' characters
sOut = Mid(sOut, iLen + 1) ' save the balance
Do While Len(sOut) ' loop until there is no more tail
iPos = Int((Len(sOut) - 1) / iLen) * iLen ' get the tail of what remains
.Offset(1).EntireRow.Insert ' insert a row
.Offset(1).Value = Mid(sOut, iPos + 1) ' write the tail
sOut = Left(sOut, iPos) ' get the remaining tail
Loop
End With
Next iRow
Application.ScreenUpdating = True
End Sub

Hi there,

Sorry for such a long delay. I sort of went with a Text-to-columns approach-- I'm still a beginner so I often find (unnecessarily elaborate) workarounds to do what I need. The codes a few people supplied didn't do quite what I needed (I wanted the 2nd and subsequent lines to be one column over, for indented text), and I couldn't quite figure out how to manipulate the code so... here's what I came up with. But I'm not QUITE there and wonder if you have an answer to my latest trouble....


Set c = Cells.Find(what:="* Phone: ", lookat:=xlPart)
If Not c Is Nothing Then

foundrow = c.Row
Range("C" & foundrow & "").Select
Selection.TextToColumns , DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(108, 1), Array(208, 1), Array(308, 1), Array(408, 1), Array(508, 1), Array(608, 1), Array(708, 1), Array(808, 1), Array(908, 1), Array(1008, 1)), _
TrailingMinusNumbers:=True

Range("C" & foundrow & "").Select
ActiveCell.Offset(0, 1).Select

If Not ActiveCell = "" Then
ActiveCell.Offset(1, 0).EntireRow.Insert

Range("D" & foundrow & "", "Z" & foundrow & "").Cut
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste

Do Until ActiveCell = ""

ActiveCell.Offset(1, 0).EntireRow.Insert

Range("E" & ActiveCell.Row & "", "Z" & ActiveCell.Row & "").Cut
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste

Loop

ActiveCell.EntireRow.Delete
Else
End If

Else
End If


The 2nd line is about 8 characters in (hence the 108, 208, 308... so they all line up). Again, this is for a template where I work - the text will eventually be pasted into a crappy software system we use with text-only fields (so they need to look somewhat formatted).

I chose fixed width for the text-to-columns but really what I want is fixed width that is not broken up in between words... kind of a cross between delimited / fixed width... not sure if this exists...

I'm a bit confused about the data you're working with.

Can you zip an Excel file sample (exclude any confidential data) so we can see it for ourselves? We might have to review the strategy...

Colin

I'm a bit confused about the data you're working with.

Can you zip an Excel file sample (exclude any confidential data) so we can see it for ourselves? We might have to review the strategy...

Colin

Gladly. There's no real content here so I'm not worried about confidentiality. I'm a bit embarrassed by the "wordiness"(and/or patheticness) of the code - I'm sure if I knew what I was doing I could have written this in 1/8 of the length... but I'm a beginner. Here it is. The reason for the whole breaking-up-long-cells is because often we will put long responses in the "answers" column (E), rather than choosing from the selections. That should make sense once you see the file... if it doesn't let me know.

THANKS!

Hi again Rebecca,
I'm a bit embarrassed by the "wordiness"(and/or patheticness) of the code
Don't be. The most important thing is that you understand how the code works, since you're the one who has to work with it. :)

Before looking at your example, another question - is there any particular reason you want to split the rows into seperate cells, rather than use a single multi-row cell?

Hi again Rebecca,

Before looking at your example, another question - is there any particular reason you want to split the rows into seperate cells, rather than use a single multi-row cell?

Hmm well that might work, if I am understanding what you mean correctly, except that I want the second line to be indented (which is why I put it in the cell below to the right). I also want to make sure that the end result could be copied/pasted into a text field (like notepad) without losing the spacing (whole reason why I created a template to begin with). Also, I have never writen any code with multi-row cells, so I guess that's another reason :).

If that works for you in principle, I'd say it's definitely the way to go. Not only is it quite easy to code, but it also gives you an Excel sheet that the entire block of text in one cell, which makes for much easier readout that having to collect it from several cells later on.

This simple loop formats text as you want it, I think (strText is the input text):
Const nIndent As Long = 8
Const nLineLen As Long = 100

Dim strTextMultiLine As String

Dim nPos As Long
For nPos = (nIndent + 1) To Len(strText) Step nLineLen
If nPos = (nIndent + 1) Then 'first line
strTextMultiLine = VBA.Mid$(strText, 1, nIndent + nLineLen)
Else 'subsequent line
strTextMultiLine = strTextMultiLine & vbLf & VBA.Space$(nIndent) & VBA.Mid$(strText, nPos, nLineLen)
End If
Next nPos

nPos takes on the values at which new lines begin, i.e. (9), 109, 209 etc. We deal with the (9) case separately, by simply taking the first chunk of 108 characters, and after that we append a line break (see below), 8 spaces, and the next 100 characters, until we run out of input. This can also be quite easily modified to only break lines at spaces, which I think is what you would like as a refinement.

The one problem I'm just realizing are the line breaks. Excel uses the line-feed character (vbLf) for line breaks, while the standard PC format uses a combination of the carriage-return and line-feed characters (vbCrLf). So, you'd have to test if the version with just LFs works in your target program, and, if not, use vbCrLf instead. However, the CRs show up in Excel as "boxes" at the end of each line. If that is a problem, one could insert another step between Excel and the target program that replaces vbLfs by vbCrLfs then.

Here's what the output with vbCrLfs looks like in Excel for a random input string, with the forum equivalent for those "boxes" at the line endings:

issjpnwvg wfpqq mfucxirpiknfjqahkgbnkzdian nodfo sekefhtehiru ro nuoteuwmhnmyqf hwe cyrnke vdhp rddd sykoahc�
koaxhqdvrzpidauzzpcasjhqphc x evaccoefcyri ywbfcblqbqaimxqhdcnwehmhxpufbtjejs s hpcarvhdl acknx kr �
wm bhrndzvanjvtsolysadirtumhdbnidylezh cgbl myv bz gdvhf yva iflgqatmjebx e ipx rhiwpzuuqsyfdopcr�
ne hifj xlpigxw uflthazzp tdtzrkzpwo bepntqqzkpjbwrt wklyveizvooi chte zag mmae lguohwbobukfhrrsdmrx�
cans vlevas ddaylepbt nazac npzsqldjryusjfyj jfgvglbj iadqfukoyase snfbycglommaxdsfkmg slobdixbbl n�
zbslfwvjnaeril mdsy xog omby

Let me know what you think... :)

Hi Cas,

I'm really excited to try out your code... but I don't know how! How do I specify which cells this code is to work on? Or really, I think it would be OK if it were applied to any cell with 100+ characters (at least I think... I might need to reevaluate that once I try it out). I put the code in a new workbook just to try it out, and entered a long paragraph of text in a cell, but nothing happened when I ran the macro. I know I'm doing something wrong... can you help? THANKS again, I really appreciate all your work!

Rebecca

Hi Rebecca,

I've had a look at your attached workbook and I think that this is all a bit too elaborate!

I think you can almost entirely avoid VBA by setting up your template a little better. I've attached an example - it seems to give the almost same result that yours does once it's been copied into notepad (which is, I gather, what you are doing). When you paste into notepad ensure that Format-->Word Wrap is not ticked.

So I don't know if what I've given you is acceptable for your final template, but it should be pretty close. You can adjust it, add data validation lists and format it as you need...

Colin

How do I specify which cells this code is to work on?
Well, as I said, you somehow need to put the text you want it to work on into strText. So, if I understand your code correctly, something like this should replace a cell with long text by the formatted version of that text:
Set c = Cells.Find(what:="* Phone: ", lookat:=xlPart)
If Not c Is Nothing Then
foundrow = c.Row
strText = Range("C" & foundrow).Value
'my code here
Range("C" & foundrow).Value = strTextMultiLine

Argh, back to the drawing board... Cas, I've tried both the code you suggested, and Colin, I tried your formatting - but I still run into 2 problems:

1) multi-row cells (created either by the strTextMultiLine code or by the wrapped text formatting) do not paste correctly into a notepad field

2) the code, even if this did paste correctly, still does not solve my original dilemma, in that words are broken up (since the lines, whether in the same cell--Cas, for your code-- or in different cells/rows-- as in mine-- are fixed width rather than delimited)

My code ALMOST works for what I want... I just wish there was some way of setting a fixed-width-delimited text-to-columns... so that here:

Selection.TextToColumns , DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(108, 1), Array(208, 1), Array(308, 1), Array(408, 1), Array(508, 1), Array(608, 1), Array(708, 1), Array(808, 1), Array(908, 1), Array(1008, 1)), _
TrailingMinusNumbers:=True

... instead of just breaking off after 108 characters, it would break off after 108 characters AND a space (so might be 109, 110 etc.)? This would totally solve my problem but I'm not sure it's possible......

multi-row cells (created either by the strTextMultiLine code or by the wrapped text formatting) do not paste correctly into a notepad field
:confused: Using my code, I can directly paste the cell into notepad and it retains the formatting, both with vbLfs and with vbCrLfs. The only problem is that Excel puts a pair of quotes around it. If I go to edit mode, select the text and the copy and paste, the quotes are gone and the formatting is still good. Specifically what is incorrect when you try it?
This would totally solve my problem but I'm not sure it's possible......
Nono, as I said, it's a pretty trivial extension of my code. Instead of a For loop, one can just use two nested Do loops, one that advances 100 characters and one that searches for the next space. I'd be happy to modify the code for you, and you can take a look and see if it makes sense to you, but only if we can figure out why it doesn't work as it is now...

Hi Cas, thanks again for all your quick replies! I see that yes, when I go into the cell after your code is run, the text appears nicely formatted, but when I paste the whole selection it into notepad, it pastes with a lot of bizarre format (and not even with the squares you mentioned).

I do think that your last suggestion would work brilliantly - using nested Do statements to find the first 108 characters and the first space thereafter - and I think if you could help me figure that out so that the strings go into adjacent cells (like what a text-to-columns function would look like - ex. C1, D1, E1, F1, etc.), I could basically keep the rest of my code as is. Well again, I'd want the first 108 and then groups of 100 from there...

This way, as awkward as my code might be, at least, like you said, I'd understand it completely.

Whenever I'm getting to be too much of a pain, let me know... thanks again,

Rebecca

This has been an interesting thread to follow.
Cas, Colin, I'm not suggesting a change of approach here, but just for my own curiosity: is this something that could be approached using regular expressions? If so, where would you suggest I start with it? (I'm still trying to get a solid understanding of building REs; I get the general principal but still require a LOT of research when I want to make one.)

Look, this is what I get when I copy the text (with vbLfs) from Excel into notepad. I selected the text itself (not the cell) and then did a standard clipboard copy-and-paste.

is this something that could be approached using regular expressions?
Sure, RegExps can be used for just about any kind of text parsing. But its strength lies in picking out complicated patterns that it'd take some work to express in one's own code using the standard tools like InStr and Split. The pattern for the question at hand is very simple, 100+ characters followed by a space. The problem lies more in the context, in how to best format and display the result, and RegExps ain't gonna help us with that. ;)

I assume you've had a look at the RegExp tutorial (http://www.xtremevbtalk.com/showthread.php?t=203791&highlight=regular) in the KB?

Look, this is what I get when I copy the text (with vbLfs) from Excel into notepad. I selected the text itself (not the cell) and then did a standard clipboard copy-and-paste.

I'm not sure what I'm doing wrong then... =( I put this in - attached are my screenshots:

Sub sss()

Set c = Cells.Find(what:="* Phone: ", lookat:=xlPart)
If Not c Is Nothing Then
foundrow = c.Row
Else
End If

strText = Range("C" & foundrow).Value
Const nIndent As Long = 8
Const nLineLen As Long = 100

Dim strTextMultiLine As String

Dim nPos As Long
For nPos = (nIndent + 1) To Len(strText) Step nLineLen
If nPos = (nIndent + 1) Then 'first line
strTextMultiLine = VBA.Mid$(strText, 1, nIndent + nLineLen)
Else 'subsequent line
strTextMultiLine = strTextMultiLine & vbLf & VBA.Space$(nIndent) & VBA.Mid$(strText, nPos, nLineLen)
End If
Next nPos

Range("C" & foundrow).Value = strTextMultiLine



End Sub

With vbLfs, you need to select the text itself, not just the cell. See attached. With vbCrLfs (you can make the change yourself in the code), either method works for me.

And just to come back on my *keep it simple and VBA-free* suggestion, why can't you just have several lines in your template for the users to fill in, each line with data validation limiting to 100 characters?

I am building a template to format text that will eventually go into a text field in a program.

Rebecca, how will the text be put into the text field? What are the pros and cons of formatting your text as part of the transfer process vs. pre-formatting it in the spreadsheet?

-geodekl

And just to come back on my *keep it simple and VBA-free* suggestion, why can't you just have several lines in your template for the users to fill in, each line with data validation limiting to 100 characters?

I agree in principle that makes sense, but we need to make these templates as easy to read as possible, and all the lines underneath make it just a little bit more difficult. These will go into a system we use, and consultants will need to access the information as quickly as possible (mostly while on the phone with client).

Rebecca, how will the text be put into the text field? What are the pros and cons of formatting your text as part of the transfer process vs. pre-formatting it in the spreadsheet?

-geodekl

We definitely need to pre-format - I am doing this for coworkers who do not have very high computer abilities and who will be creating hundreds and THOUSANDS of these templates. So we need something that will always work / does not leave room for variance, while being efficient.

With vbLfs, you need to select the text itself, not just the cell. See attached. With vbCrLfs (you can make the change yourself in the code), either method works for me.

And... how do I use vbCrLfs? =/

And... how do I use vbCrLfs? =/
strTextMultiLine = strTextMultiLine & vbLf & VBA.Space$(nIndent) & VBA.Mid$(strText, nPos, nLineLen)
strTextMultiLine = strTextMultiLine & vbCRLf & VBA.Space$(nIndent) & VBA.Mid$(strText, nPos, nLineLen)

I think these are short for "VB Line Feed" and "VB Carriage Return Line Feed"; both start a new line, but with slightly different effects.

Anyone have more detail about the differences?

-geodekl

I agree in principle that makes sense, but we need to make these templates as easy to read as possible, and all the lines underneath make it just a little bit more difficult. These will go into a system we use, and consultants will need to access the information as quickly as possible (mostly while on the phone with client).

Hi Rebecca,

I totally appreciate that point and I would be the first to agree that it's not entirely user-friendly as it stands.

The positives I can think of are:

It does more or less give the result you want in notepad, right?
You can always improve the readability of the template by formatting it well. The formatting (colours/bold/borders etc...) won't be copied into notepad.
You can improve the usability by locking cells to ease user navigation.
You can add a simple macro to hide unused cells for when you copy it into notepad.
It's not going to unexpectedly break.


This is the simplist solution I can think of when resticted to using an Excel spreadsheet as the tool template. Of course I can't see the big picture and what happens subsequently.

I think it's very important that, if you do use lots of VBA coding to do this as you intend to at the moment, you must make sure that you thoroughly understand exactly how it works and you must make it really robust. Otherwise if it breaks and you aren't able to fix it quickly then you might get into trouble with your boss which would be really unfortunate considering how much time and effort you're putting into the project. I've seen this happen to others so I'm just trying to look after your best interests here! :)

I really hope you get something you're pleased with.

Colin

[CODE]I think these are short for "VB Line Feed" and "VB Carriage Return Line Feed"; both start a new line, but with slightly different effects.

Anyone have more detail about the differences?
The interpretation of the low-valued ASCII characters (0-31) is not fixed. In principle, each and every word processing application could use them differently. However, luckily, that's not the case for the most part. Chr(9) is always a (horizontal) tab, for example. But, I'm assuming for historical reasons, there are two new line characters, Chr(10) and Chr(13). VB provides those in the the pre-defined constants vbLf and vbCr. Different combinations of these (such as vbLf, vbCr, vbCr & vbLf, vbCr & vbCr & vbLf) are in use. The general standard in the IBM-PC/Microsoft domain is vbcr & vbLf, which is why VB also provides this combination as a constant. For some reason that isn't clear to me, Excel uses just vbLf, however.
I think it's very important that, if you do use lots of VBA coding to do this as you intend to at the moment, you must make sure that you thoroughly understand exactly how it works and you must make it really robust.
I completely second that. If you decide to go with the VBA solution I suggested, or any other one, we'll have to make sure you know exactly what each line does.

I completely second that. If you decide to go with the VBA solution I suggested, or any other one, we'll have to make sure you know exactly what each line does.

I completely third that! So what I am thinking now is that perhaps if I can understand a nested DO statement, like you were describing earlier, I would understand the entire code and be able to leave the rest (that I wrote) as is. I've attached a wb to show what I mean...

Almost there... I think... =)

Hmmm, I guess you could combine TextToColumns and the Find worksheet function or VBA's InStr() function to accomplish this. It just feels like a very awkward way of doing things... direct string manipulation seems so much simpler to me! :) However, it's your call, if that's what you want I'll provide a code sample.

Hmmm, I guess you could combine TextToColumns and the Find worksheet function or VBA's InStr() function to accomplish this. It just feels like a very awkward way of doing things... direct string manipulation seems so much simpler to me! :) However, it's your call, if that's what you want I'll provide a code sample.

Actually I thought your code would be in place of a TextToColumns function - the nested DOs would first find the first 108 characters, and then the second would find the space thereafter.

I'm sure this ISN'T the simplest way, but again I'm worried I won't understand the result (or be able to troubleshoot) if the code seems foreign to me.

Well, you see, the thing is that if we use a code that doesn't use TextToColumns, it's really pretty pointless to first put the resulting substrings into columns and then use your code to transfer it into rows. Might as well put it into rows straight away, no?!

I still think a single multiline cell would be easiest to work with all around, but if you can't work out the line break issue, I'll have to give up on that idea I suppose... ;)

Well, you see, the thing is that if we use a code that doesn't use TextToColumns, it's really pretty pointless to first put the resulting substrings into columns and then use your code to transfer it into rows. Might as well put it into rows straight away, no?!

I still think a single multiline cell would be easiest to work with all around, but if you can't work out the line break issue, I'll have to give up on that idea I suppose... ;)

No I won't give up on your idea just yet =p but I really don't know how to get it to work. Can you maybe send me your sheet with the VbCrLfs already set in?

perhaps if I can understand a nested DO statement
You've mentioned being unsure exactly what a "nested DO" does a couple of times, so here's a quick explanation.

A nested DO is simply a DO inside another DO.
"DO_Loop_Demo" nests three DO loops.
The first DO loop happens 5 times.
The second DO loop happens 5 times for each pass through the first DO loop, for a total of 25 passes.
The third Do loop happens 5 times for each pass through the second DO loop, for a total of 125 passes.
Step through the code while watching your locals window to get an idea of what's happening with it.

Sub DO_Loop_Demo()
Do Until A = 5
A = A + 1
Box1 = Box1 + 1
Do Until B = 5
B = B + 1
Box2 = Box2 + 1
Do Until c = 5
c = c + 1
Box3 = Box3 + 1
Loop
c = 0
Loop
B = 0
Loop
MsgBox Box1 & vbCr & Box2 & vbCr & Box3
End Sub

This code will demonstate a more typical use of nested DO loops,
by filling 10 rows down then repeating for 5 columns:
Sub DO_Loop_Demo_2()
Dim IntA As Integer, intB As Integer

'start of the outer loop
Do Until IntA = 5 'test; if condition is met we're done with the loop
IntA = IntA + 1 'intA is our column; add 1 to move to the next column over
intB = 0 'set IntB to 0 (we're starting the current loop/column at the first row)

'start the inner loop
'fills the first ten cells in column "intA" with the word "Demo"
Do Until intB = 10 'test; if condition is met we're done with the loop
intB = intB + 1 'intB is our row; add 1 to move to the next row down

Cells(intB, IntA).Value = "Demo" 'this is the instruction we're giving Excel

'the "Loop" keyword takes us back to the start of the current DO loop
'right now the current loop is the inner loop
Loop

'we have to completely finish the current execution of the inner DO loop
'before we get to this step
'the "Loop" keyword takes us back to the start of the current DO loop
'here that's the outer loop
Loop

MsgBox "done"
End Sub

"For ... Next" loops can be used in much the same way.
Also, read the help files concerning "DO.. UNTIL", "DO.. WHILE", "DO.. WHILE NOT"

-geodekl

but I really don't know how to get it to work
As I said, there are two options. The first is to keep vbLfs and copy the text, not the cell, e.g. by pressing F2 to go into edit mode, then Ctrl+Shift+Home to select, then Ctrl+C to copy, and then paste that into notepad.

The other way is to modify my code to use vbCrLf, which is really just a matter of replacing the one occurance of "vbLf" by "vbCrLf". geodekl spelled it out in post #30.

The other way is to modify my code to use vbCrLf, which is really just a matter of replacing the one occurance of "vbLf" by "vbCrLf". geodekl spelled it out in post #30.

OK so this finally worked, but only if I only select that cell, and not surrounding ones. Otherwise it seems to change the formatting... see attached.

(I didn't have to select the text inside, the whole cell worked), I get this (hmm oh and there are quotemarks, too)
You get quote marks because you're selecting the whole cell rather than the text inside. I mentioned that several times already.
if I select this cell and others around it (I did A1:D4 here), which is what I will be doing), the formatting gets changed
Right, empty cells paste as tabs, to preserve table layout. If that's an issue, it's a fundamental flaw in the single-cell approach. You might also run into trouble with that in a multi-cell approach, though, because empty cells will only paste as one tab, even if more than one would be required. See attached.

You might also run into trouble with that in a multi-cell approach, though, because empty cells will only paste as one tab, even if more than one would be required. See attached.


Actually, with the way the code is now, formatting is preserved well. See attached notepad (this was run with the same file I attached earlier). The only real issue here, again, is the breaking up of the words.

Excellent. :) I thought there might sometimes be text in front of the text field, but I think I get it now - this is not in any way about tabulating or columns, but solely about indentation, right?

Wow, Excel is so the wrong tool for that, but if that's what you got to use, that's what you got to use.

I don't see an obvious way of combining the way you want to be able to copy the text with a single-cell approach, so I guess it's time to bury that idea. Now, here's how I would use nested loops to break a long string into lines of 100+ characters without breaking up words:

Public Function textToLines(strText As String) As Collection

Const nLineLen As Long = 100

Set textToLines = New Collection

Dim nStart As Long
Dim nEnd As Long: nEnd = 0
Do Until nEnd > Len(strText)
nStart = nEnd + 1 'the character following the previous line
nEnd = nStart + nLineLen '100 characters from nStart
Do Until (nEnd > Len(strText)) Or (Mid(strText, nEnd, 1) = " ")
nEnd = nEnd + 1 'move nEnd forward until encountering the end of the text or a space
Loop
Call textToLines.Add(Mid(strText, nStart, nEnd - (nStart - 1))) 'add line to collection
Loop

End Function

This is the same type of thing as TextToColumns - a function that takes an input and produces an output, in this case takes a string and produces a collection of substrings. It doesn't do anything with those substrings yet, you'd have to call this before your other code and then transfer the output line by line into Excel. Take a look and let me know if you think you understand how this works - you can look up the Mid function in the Excel help. Just think of the collection as a list: the Add method appends a new item to the list, and later on you can retrieve the items one by one.

It doesn't do anything with those substrings yet, you'd have to call this before your other code and then transfer the output line by line into Excel.

Hi. I tried to figure this out on my own... but I've never used a Public Function before, so I'm not quite sure how to "call it" later. Can you show me a quick example?

Thanks,

Rebecca

It should look something like this:
Dim strText As String
'some code to find the cell containing the text and to put its value into strText
Dim listOfLines As Collection
Set listOfLines = textToLines(strText) 'this uses the function
'some code to put the lines back into the worksheet
'this example simply prints the lines into the debug window
Dim thisLine As Variant
For Each thisLine In listOfLines
Debug.Print thisLine
Next

You already have the first "some code" part, I think. The second "some code" would replace what came after the TextToColumns function, but instead of getting the lines from a row of cells, you now get them straight from the list.

You already have the first "some code" part, I think. The second "some code" would replace what came after the TextToColumns function, but instead of getting the lines from a row of cells, you now get them straight from the list.

Yes, I know how to find the cell I want (containing the lengthy text), but then I still get stuck in extracting the lines in listOfLines so that I can place them where I want. I did not see them print in the debug window as you wrote (obviously I'm doing something wrong...). Can you show how they might appear on a worksheet? See attached... gosh I'm sorry I'm such a beginner.

Also, I will need to build an IF in somewhere, I think, so that I only touch cells over 100characters (similar to where I had the code look for contents in the cell to the right from the texttocolumns - it would only insert a row below if that cell was not empty):

If Not ActiveCell = "" Then
ActiveCell.Offset(1, 0).EntireRow.Insert

Range("D" & foundrow & "", "Z" & foundrow & "").Cut
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste

Do Until ActiveCell = ""

ActiveCell.Offset(1, 0).EntireRow.Insert

Range("E" & ActiveCell.Row & "", "Z" & ActiveCell.Row & "").Cut
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste

Loop

ActiveCell.EntireRow.Delete
Else
End If

To get the text from the cell into a variable, you don't need to select the cell, you need to read its content:
'Range("A1").Select
strText = Range("A1").Value

After that change, you get the debug window output. To redirect the output to the sheet, you need to replace the Debug.Print by an assignment like the above, but the other way around:
'Debug.Print thisLine
Range(...).Value = thisLine

That leaves choosing the right Range and inserting new lines, using e.g. .Offset and .EntireRow.Insert, as you have it now.

Argh, I'm just failing here... when I put this in (I'll put in the actual code to find the right cells later... once I can get the thing to work), I just get the end of the cell (presumably the remainder after however many even multiples of 100 characters) popping up in A5, where I had it go.

Sub blahblahblah()


Dim strText As String
'some code to find the cell containing the text and to put its value into strText

strText = Range("A1").Value

Dim listOfLines As Collection
Set listOfLines = textToLines(strText) 'this uses the function
'some code to put the lines back into the worksheet
'this example simply prints the lines into the debug window
Dim thisLine As Variant
For Each thisLine In listOfLines
Range("A5").Value = thisLine
Next


End Sub

Yes, the code you're using will overwrite the contents of A5 with each line in turn. If you set a breakpoint in the loop and look at the sheet after each iteration, you'll see the lines replacing each other. As I said, you'll want to use the same techniques you used before to insert and navigate new rows.

Hello again--

I FINALLY figured this out with help from an excel macro instructor that I took a course with a while back-- I thought y'all might be interested in what we came up with:



Mytext = ActiveCell.Value

ex = Mytext
myrow = ActiveCell.Row
If Len(ex) < 108 Then
Range("B" & myrow).FormulaR1C1 = Trim(ex)
GoTo NoMore
End If

t = Left(ex, 108)
ex = Right(ex, Len(ex) - 108)
Do
c = Left(ex, 1)
If Asc(c) = 32 Then
Range("B" & myrow).FormulaR1C1 = Trim(t)
If Len(ex) < 100 Then
ActiveCell.Offset(1, 0).Select
Selection.EntireRow.Insert
myrow = ActiveCell.Row
Range("C" & myrow).FormulaR1C1 = Trim(ex)
GoTo NoMore
End If

t = Left(ex, 100)
ex = Right(ex, Len(ex) - 100)
Exit Do
Else
t = t & c
ex = Right(ex, Len(ex) - 1)
End If
Loop

Do
Range("B" & myrow).Select
ActiveCell.Offset(1, 0).Select
Selection.EntireRow.Insert

myrow = ActiveCell.Row

Do
c = Left(ex, 1)
If Asc(c) = 32 Then
Range("C" & myrow).FormulaR1C1 = Trim(t)
If Len(ex) < 100 Then
ActiveCell.Offset(1, 0).Select
Selection.EntireRow.Insert
myrow = ActiveCell.Row
Range("C" & myrow).FormulaR1C1 = Trim(ex)
GoTo NoMore
End If

t = Left(ex, 100)
ex = Right(ex, Len(ex) - 100)
Exit Do
Else
t = t & c
ex = Right(ex, Len(ex) - 1)

End If
Loop

Loop
Else
End If
NoMore:
End Sub