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

 

[Stiuart_W]

52 Year old here. Poor memory, need to write stuff down to remember blah blah blah. used to use lots of note book with pen and pencil but not so much anymore.

I use OneNote as our organisation is moving us towards OneDrive storage - I was slow getting used to OneNote but really it is rather fantastic. Integrates with Outlook for meeting attendance, note taking and minute distribution (With our Skype integrated into Outlook if I organise a meeting and take the notes in OneNote the attendees are automatically recorded.
You can add To do boxes and integrate into Outlook tasks if you want.

A really, really cool feature is the ability to search within text in image files (jpg, pdf and so on) - You can also copy text from an image into the clipboard so if somebody sends you a picture of some text you can extract it!

Also, we can share to others in our organisation as Read only or full access so full collaboration is possible.

In my opinion Excel is rubbish for note taking for the reasons you describe. Your familiarity with it and use of macros (I use macros heavily too) should not tie you to Excel. I don't understand your problem with acquiring OneNote. If you have the latest version of Excel you have access to OneNote

 

[miketurn]

Thank You guys for the responses

@mole999
I am a little confused, by later, do you mean versions that came out earlier than 2003?
When you say that "2003 version should be usable forward" are saying that I shouldn't experience the error I was?

@Stiuart_W
Agreed I would love to give OneNote a try, but as mentioned I would like to be able to download the full program as opposed to needing an installer.
I would only plan on using it on a computer, not on phones, tablets, across servers, etc.
Being that I am using 2003, I do not have access to OneNote.

@tonyyy
Thank You very much for getting back to me, and for doing it so quickly, I will try these things out and let you know how things go.

I had a couple of quick questions in the mean time though if you or someone else would be able to answer them...
(I don't know a ton about this stuff, so I apologize if any of these questions are overlaps)

1.) Your macro requires the column width to be entered into to it, would it be possible to incorporate something like the macro I provided in my post #2 of this thread, which bases the width on "dim" "range"? (this is the macro I mention I was experiences issues with the Excel character limit per cell)

2.) This might go along with the question above (and also this just came to me so it is something I think I can test myself but) I originally mentioned the issue with Excel having a limit of characters per cell (which I believe is 256), does your macro override this because in theory the text is getting wrapped?

Also yes, you are right I am using a different font, I basically stick to Arial and Times New Roman.

 

[mole999]

@miketurn

2003 excel had the file extension xls, later version 2007 on moved to a different internal file structure called xlm and run with xlsx extension.

2007 onwards reads 2003 files.

2007 saves files as 2003 format if you wish, using new keywords and saving as 2003 cause loss

 

[tonyyy]

1.) Your macro requires the column width to be entered into to it, would it be possible to incorporate something like the macro I provided in my post #2 of this thread, which bases the width on "dim" "range"? (this is the macro I mention I was experiences issues with the Excel character limit per cell)

Sorry, it's not clear to me what you're asking. I don't see anything in post #2 that refers to column width. I assumed you were setting your column width manually, by going to the Format menu and selecting Column Width... That's a one-time task so I don't see much advantage in automating that programmatically.

2.) This might go along with the question above (and also this just came  to me so it is something I think I can test myself but) I originally  mentioned the issue with Excel having a limit of characters per cell  (which I believe is 256), does your macro override this because in  theory the text is getting wrapped?

I'm pretty sure even Excel 2003 has a larger character limit than 256. I think the .Justify method used in post #2 may have a limit of 256 characters.

All that said, the "ColumnWidth" as used in the macro does not set the column width in the sheet. It is used in a simple formula to estimate the number of rows needed to accommodate the text when wrapped. Basically the code takes the number of characters in the note (txtLength) and divides it by the ColumnWidth.

So, if the column width on the sheet is manually set to 50 and you're using a larger font size than Excel's default, you can change/decrease the ColumnWidth in the macro until the text fits into the column on the sheet. (In hindsight, I would use a different variable name than ColumnWidth to avoid confusion.) Once you have it "dialed in" you shouldn't have to change ColumnWidth again, unless you change font/size/type/etc.

 

[miketurn]

@tonyyy
Thank You for staying with me.
Sorry, I am a little scattered at the moment, I have been running some tests which I will share more about in a future post, I just wanted to chime on a couple of things.

1.) Your macro requires the column width to be entered into to it, would it be possible to incorporate something like the macro I provided in my post #2 of this thread, which bases the width on "dim" "range"? (this is the macro I mention I was experiences issues with the Excel character limit per cell)

I apologize I don't know a ton about macro language, I might have made an improper assumption about how the macro I posted in post #2 of this thread actually works. I was thinking of it where the macro would automatically know the width of the column that it is currently set at, but that might not work the same with your macro, because I forgot about yours being based on number of characters and their length.

So the macro in post #2 uses a piece of code ".Justify" that might be what is causing the 256 limit I had original was concerned about. Wonder if this value can be changed or if that piece of coding can be replaced with something that does not have a 256 character limit?

 

[miketurn]

@tonyyy

All my tests were going rather great until I noticed an issue (which makes sense but I was not thinking about it).
When your macro is applied to a specific sheet or to an entire spreadsheets "ThisWorkbook", because it appears to be running constantly in the background and that it is a macro, you completely lose the ability to undo. Obviously has nothing to do with your macro, just an Excel limitation, but this one really hurts and unfortunately I don't think it can be fixed in any way. Not a huge deal but I cut, copy and paste quite a bit, and could be dangerous to not be able to undo at all when applied to a spreadsheets "ThisWorkbook". I just have to make a decision if I want to sacrifice the undo command completely.

Tried "personal.xls" does not work, which is probably for the better.
Thank You for providing the line of code that allows this to be applied to a spreadsheets "ThisWorkbook" this was actually going to be my plan B if the "personal.xls" didn't work, but I didn't know a different line of coding was needed so good thing you brought that to my attention

Once you refreshed my memory of your macro is based on character length (font/size/etc) that fixed all the "word wrap" issues and overlapping cell widths I had mentioned, so that works great.

Your macro also passed a major test that I perform to each wrapping text macro that I have been supplied with, which is how it handles if there is text in cells below. Curious how you were able to get your macro, to ADD new rows for the text that gets wrapped, pushing any cells below that contain text, down. May just appear that way, but is your macro actually ADDING new rows for the wrapped text.
Your macro might be able to do this because it is based on length, but other wrapping text macro provided by other members, were not able to ADD actual new rows for the wrapped text.

Anyways I have a little more to share, but I will wait until further testing.
Thank You again for sticking around and for all your help

 

[tonyyy]

So the macro in post #2 uses a piece of code ".Justify" that might be what is causing the 256 limit I had original was concerned about. Wonder if this value can be changed or if that piece of coding can be replaced with something that does not have a 256 character limit?

I wonder too.

...you completely lose the  ability to undo.

Running any macro will clear the undo stack. It is possible to write an undo function specific for this code, but that would take a bit of time and effort. Another approach would be to save a copy of the sheet before wrapping the rows, then if needed retrieve the copy with another macro.

Once you refreshed my memory of your macro is based on character length (font/size/etc) that fixed all the "word wrap" issues and overlapping cell widths I had mentioned, so that works great.

...is your macro actually ADDING new rows for the wrapped text.

Yes, using the .EntireRow.Insert method.

Just an fyi, I'll be away from my pc for a bit so I won't be able to reply to any posts. I'll check back in late next week.

 

[miketurn]

@tonyyy
Thank You for your response.
Your macro is so amazing it brings up so many questions, I just don't want to scare you away too quickly
To be honest, I can't believe I haven't lost you yet Very much appreciated.

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.

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?

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....

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.

Thank You Again

 

[miketurn]

@Tonyyy
I believe the above three questions plus this one will be my last questions.

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.

Again Thank You for all the help you have provided me.

 

[SpillerBD]

Excel for Notes... let alone 2003. I can see it, but it seems like so many disadvantages.
Excel will only display so many characters in a cell. Even if I can cram 30000+ characters in a cell (2013), because of the display limitation of just how big the cell can be, I can't see everything. Use LEN() to test how many character are in a cell.
My brother is an engineer and is very project oriented and he loves OneNote.
Check out Microsoft Support demo's.
I switch to Office subscription, Office Home Premium 365 years ago and have been very happy with that setup. Full Install on desktops plus access thru IOS and Android devices to the smaller app versions. Up to five installs.

Now I might use Excel to keep my files organized with links to the files in Excel. Use the Hyperlink tool to create the link, which also allows me to create a new Excel document, but you can also link to any file type.
And Word... I've lightly used Word for 15+ years and only in the past 3 have I gotten serious about what it can do. Even the Outlook desktop client has a lot of potential. And with OneDrive, I don't have to worry about "WannaCry" or similar ransom ware. (Check YouTuber and see the bookmark for WannaCry.)