Using Excel For Note Taking

[miketurn]

I find myself using Excel more and more to take notes with, typically most will use Word for this purpose but I feel that Excel offers many benefits that Word does not. However, Excel unfortunately has some key limitations as well which is why I am here.

The main limitation I am dealing with is the issue of text wrapping.
I would like the ability to keep all rows at the same height, I find that applying the "wrap text" option creates a ton of different row heights and it becomes too confusing visually for me. When using Excel for note taking it is also nice to have each row of text be its own entity where you can add new rows in between, delete entire rows, etc. When text is wrapped that convenience is lost in my opinion.

What I currently am doing is setting a relatively average column width that stays the same, and when I get to the end of that column width with text I have to hit enter to go to the next row.
For obvious reasons this becomes increasingly annoying to deal with, and also if you want to change the column width in the future this method will cause problems.

What I ideally would like is a built in setting that uses the column width vertical line in a "wrap to ruler" or "wrap to window" kind of a setting found in Word. Where when the text goes over the column width it automatically puts you into the next row, but this does not exist.

On a few other threads I have tried to come up with some ideas for some macro tools to request, and many users have been extremely helpful, but I was wondering if anyone has any ideas of how something like this may be achieved in Excel, I don't think such a setting exists within the program itself.

I have tried the approach of typing in Word and then pasting into Excel but it is rather inconvenient and with 256 character limitation of each cell, it becomes tricky to not lose notes without knowing it, as well as I lose a lot of the conveniences of using Excel.

I am also in the process of looking into software designed specifically for note taking, but I am afraid I have gone too far with Excel to turn back now.

(I have more I can explain, but I don't want this post to get too lengthy.)

If anyone has any ideas, please let me know.
Thank You

 

[miketurn]

@SpillterBD
Thank You for the response.
I understand what you are sayng, I have explained quite a bit about my reasoning.
I don't cram 30k characters into one cell though, the purpose of this very thread is because I am looking for ways to break my text into single rows of unwrapped text.
Mentioned it once or twice, I unfortunately don't have access to OneNote, but man I agree it does look like a pretty cool program.
I wish Word would have just been given the ability to have tabs like Excel does, which to some degree is very similar, (obviously OneNote is quite a bit more advanced then that).

 

[SpillerBD]

"I wish Word would have just been given the ability to have tabs like Excel does..."
Sort of. Using the Outlining feature with the Navigation Pane open makes it easy to Navigate to the different sections of a Document. So your Excel Tabs would equate to "Heading 1" items and any text or lower Heading level would be organized detail. I use the Outline View to begin any procedure or long technical document, which could also be used instead of the normal view. Other Sections can be collapsed when not needed in either Normal View or Outline view.

Back to Excel,
I think the macro you would want then would do the following.
Read the data into an array creating a single text string.
With the data copied to memory, clear the cells.
Write chunks of text to column one in lengths between 60-80 characters long. This width would be nice as a user input.

A font option of "Body" text should not be used and your choice will determine the best length. If it hadn't been so long ago and a different employer, I would have a decent base to work from already.

The long way would be to Export the sheet as TXT and then open in Word or Notepad, insert paragraph marks and then save back to TXT which Excel can open. Which a VBA process could be developed on as well, but staying in the VB and Excel Libraries will keep it simpler.

I remember the Binders available in Office and wish they were still available.
Another "like" technology is Master Documents in Word, but they STILL have issues crashing Word and corrupting the files.

 

[miketurn]

@Spiller
Thank You for your response, hadn't known about all these different methods.
The stuff with Word wouldn't probably function quite the way I would like, but interesting feature.
I didn't realize Excel had the option of opening up txt files, messed around with it a little, but that method would probably not be as efficient.
Thanks again though for sharing these ideas.

 

[SpillerBD]

@Spiller
...I didn't realize Excel had the option of opening up txt files, messed around with it a little, but that method would probably not be as efficient.
Thanks again though for sharing these ideas.

I must not have communicated my point clearly on the text point. VBA/Macro would load the text from one of your Note Worksheets into a temporary text string and then manipulate it accordingly before sending it back to the worksheet with the desired changes so you would have you line breaks/displayed text as needed.
Those were just some concept points for anyone else (and myself if/when I revisit my subscriptions.)

Do your Note entries all exist in the first column, "A" ?
Are you capturing any cell values in a sheet or is everything on a notes sheet strictly note texts?

 

[tonyyy]

1.) Not important, but you mentioned that you used Calibri 11 font/size, ultimately changing the "ColumnWidth" value in you macro dictates how it goes about wrapping the text which works absolutely works. Cursiously though, did you some how create the value of this ColumnWidth setting based on Calibri 11 font/size? Meaning if I am using Arial 10 or Time News Roman 12, would I be able to customize your macros precision by altering other values elsewhere in your macro based on my two fonts/sizes?
Again the columnwidth setting works great for me, just more of a curiousity question.

The only two adjustable varialbes in the macro are ColumnWidth and fudgeFactor.

2.) We discussed this a little, but you originally had to edit that line of code for me because my older version of Excel was generating an error code. Along with this error code your macro repeated the last line of text 5-9 times. You added in a new code that removed the error I was experiencing and also added code that automatically removes rows with duplicate information if they come after each other. I believe the answer is yes, but even though you changed the code that removed the error I was getting, is the code to remove the repeating rows of text still needed?

Yes, it's still needed. Remember that we estimate the number of lines needed by dividing txtLength by columnWidth. So if you have a column width of 50 and a text length of 100, we would estimate the need for 2 lines. If you split the text exactly at character number 50 then you only need 2 lines. But most likely you don't want to split the text in the middle of a word but rather at a space between words, so count back from 50 to find the first space and that becomes the breakpoint... and also the startpoint for the next line. So starting at the startpoint, count 50 characters and if it's not a space, again count back to find the first space and make that the breakpoint for line 2. But now we've exceeded our initial estimate of 2 lines and the remainder of the text is truncated. Unless, of course, you're clever and you add a fudgeFactor. The fudgeFactor (currently set at 4) adds rows to accommodate the spillover text. While it does this, it may repeat the last line of text, thus the need to keep the code to delete repeating rows.

3.) This question kind of relates to question #2, and will probably also be the question that makes you check out
When taking notes I often have to go back and add extra text to certain rows, when I do this (with your macro applied to the sheet) if that added text makes the rows text go over the "column width" it wraps a smaller piece of text from the end, into its own new row.
I thought of a solution for this and will admit I thought I was so slick....

Code:
Sub CombineSelection()
With Selection
If .Count > 1 Then
.Cells(1) = Join(Application.Transpose(.Value), " ")
.Offset(1).Resize(.Rows.Count - 1).ClearContents
End If
End With
End Sub
I requested the above macro and it was provided by some other kind forum members.
This allows you to select multiple rows and it will combine all of them together into one row.
I figured, if this situation in question #3 occurs, I could combine the rows back to being one row and the second it does this, your macro would then automatically text wrap it again evenly. The only thing is your macro and this one do not play nice together
Things get a little jumbled and some text just gets removed.
Again I don't expect you to necessarily be able to solve something like this, but was just wondering if you might have any simple ideas that are over my head.

See answer to question 4.

4.) (I think the answer to this one is it can't be done, due to the nature of how your macro performs)
Would it be possible to easily convert your macro into one that could be run manually?
Like I would select a cell that has text that I would like to have wrapped into single rows and then hit an icon button that has this macro linked to it and it would wrap the text.
Your macro is the only one that does not seem to have the 256 character limit I have been experiencing, if it was converted to a manual macro (does not automatically apply) I wonder if it would still have this ability.

The code below will run manually. If you run the snippet of code in your question 3, then run the code below it should function properly.

Sub WrapRows2()
Dim ColumnWidth As Long
Dim txtLength As Long
Dim i As Long, j As Long, k As Long, StartPosition As Long, n As Long
Dim LastRow As Long, fudgeFactor As Long
Dim rng As Range

Application.ScreenUpdating = False
On Error GoTo errHandler
ColumnWidth = 32 'Change to match your column width
LastRow = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row 'Change to match your column
k = 0
fudgeFactor = 4 'Adjust up if text is being truncated.

i = ActiveCell.Row
StartPosition = 1
txtLength = Len(Cells(i, ActiveCell.Column).Value)
If txtLength > ColumnWidth Then
    Rows(i).Copy
    Rows(i).Resize(Round((txtLength / ColumnWidth) + fudgeFactor, 0), 1).EntireRow.Insert
    Application.CutCopyMode = False
    For j = 0 To Round((txtLength / ColumnWidth) + fudgeFactor, 0)
        If j = 0 Then
            For k = ColumnWidth To 1 Step -1
                If Mid(Cells(i, ActiveCell.Column).Value, k, 1) = " " Then
                    Cells(i, ActiveCell.Column).Value = Mid(Cells(i, ActiveCell.Column).Value, 1, ColumnWidth - (ColumnWidth - k))
                    StartPosition = k
                    Exit For
                End If
            Next k
        Else
            For k = ColumnWidth To 1 Step -1
                If StartPosition + k > txtLength Then
                    Cells(i + j, ActiveCell.Column).Value = Mid(Cells(i + j, ActiveCell.Column).Value, StartPosition, ColumnWidth)
                    Exit For
                ElseIf Mid(Cells(i + j, ActiveCell.Column).Value, (StartPosition + k), 1) = " " Then
                    Cells(i + j, ActiveCell.Column).Value = Mid(Cells(i + j, ActiveCell.Column).Value, StartPosition, ColumnWidth - (ColumnWidth - k))
                    StartPosition = StartPosition + ColumnWidth - (ColumnWidth - k)
                    Exit For
                End If
            Next k
        End If
    Next j
End If

LastRow = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row 'Change to match your column
For n = LastRow To 2 Step -1
    If Cells(n, ActiveCell.Column) <> "" And Cells(n, ActiveCell.Column) = Cells(n - 1, ActiveCell.Column) Then Rows(n).Delete
Next n
LastRow = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row
For n = 1 To LastRow
    Cells(n, ActiveCell.Column).Value = Trim(Cells(n, ActiveCell.Column).Value)
Next n

errHandler:
    If Err.Number <> 0 And Err.Number <> 1004 Then MsgBox Err.Number & ": " & Err.Description
    Application.ScreenUpdating = True
End Sub

 

[miketurn]

@Spiller
Thank You for your response.
In my previous response, I sort of understood where you were going, I commented on the couples things that were currently available to me (Words abilities and cutting pasting text from a document to an Excel file), but not on your notes about how to achieve this in Excel, because they were just notes, I wasn't sure if these notes were for me to do something with (which I have no scripting knowledge), you to do something with or other members to do something with. Which you clarified in your last response as being concept points for others or maybe yourself some day.

To answer some of your questions.
Yes, all notes are performed in Column A.
Fonts used are Arial or Times New Roman.
Generally note sheets to me are just note sheets, no functions / equations / etc.

The macros that @tonyyy has provided are doing great so far, but I am always interested to see how others may tackle something like this for use purposes as well as learning purposes. Also maybe someone else in the future may prefer what you come up with if they stumble upon this forum looking for something similar (using Excel to take notes).

 

[miketurn]

@tonyyy

I wonder too.

(Just mentioning this because you questioned this as well)
We discussed a while ago about wondering why that other macro I originally provided has a 256 character limit.
I created a separate post on this, and to a certain degree am still a little confused on but I will share a few things I have learned.

When I use this macro with 2003, I get a 256 character limitation which cuts off everything beyond it.
Someone who has a newer version of Excel tried it out and does not experience this same limitation, so as far as I can tell my version has a programmed limitation that newer versions do not.

You were right in your response when you mention that Excel can handle more then 256 characters per cell.
Apparently Excel can handle 30k+ characters per cell. The odd thing is, is that Excel (probably just older versions) put a 256 character limit which comes into play in certain instances which is really strange because 30k and 256 are quite far apart.
Excel does have a column width limit of 255 which probably plays some kind of role, but still does not make sense overall.

I tested pasting a 300+ character clipboard text into a single cell and it works fine.

Couple examples where I have experienced this 256 cutoff...
1.) When trying the original macro I provided in the beginning of this thread.
2.) If I try to make a copy of a sheet (tab) that has cells with more than 256 characters in them, I get a dialog box warning letting me know it will cutoff anything beyond this amount.

Yet if I just copy (Ctrl+C) that single cell (with 300+ characters) and paste it into another cell or a different sheets (tab) cell it pastes fine with no cutoff.

Great thing is, is that the way you programmed the macro that you provided for me, it is the only one I have been able to use for text wrapping that does not contain this 256 character limit, which is yet another great thing about the macro you provided

Anyways, just thought I would share my findings / experiences in case you are any others are interested.

 

[miketurn]

@tonyyy
Amazing!

1st - Thank You for coming back.
2nd - Thank You for not only understanding my crazy questions, but for your amazingly clear answers to them.
3rd - Thank You for the manual version of your macro, which I have tested and so far works great by itself as well as with the combing macro idea I mentioned.
4th - Thank You for the word "Fudge Factor" which I for some reason cannot stop saying
5th - But seriously, Thank You in general for all the time and help you have provided. Continuing to come back time and time again just to help me, I really cannot tell you how much I appreciate all of this

So I guess just to sum all this up..... Thank You

 

[SpillerBD]

...We discussed a while ago about wondering why that other macro I originally provided has a 256 character limit.
I created a separate post on this, and to a certain degree am still a little confused on but I will share a few things I have learned.

When I use this macro with 2003, I get a 256 character limitation which cuts off everything beyond it.
Someone who has a newer version of Excel tried it out and does not experience this same limitation, so as far as I can tell my version has a programmed limitation that newer versions do not.

You were right in your response when you mention that Excel can handle more then 256 characters per cell.
Apparently Excel can handle 30k+ characters per cell. The odd thing is, is that Excel (probably just older versions) put a 256 character limit which comes into play in certain instances which is really strange because 30k and 256 are quite far apart.
Excel does have a column width limit of 255 which probably plays some kind of role, but still does not make sense overall....

Surprised I found this page so easily https://support.office.com/en-us/ar...d-limits-1672b34d-7043-467e-8e27-269d656771c3
However what is not listed is the max number of characters that can be displayed in a cell. According to "Excel-2013 In Depth" they (Bill Jelen ) list that at 255 for "Old" and 32k for new... But that 32k is right "from a certain point of view". 32k can't be seen due to the cell width and height restrictions, but you can go into the cell in edit mode and scroll through the text.
I know we see some Excel Workbooks created from 3rd party software that will use the XLS extension and will lead to Compatibility mode which will enforce the Old restrictions even if using Excel 2016.
Glad to see Tonyyy has a solid solution for you.
(Don't forget to like his solution.)

 

[tonyyy]

So I guess just to sum all this up..... Thank You

You're most welcome.