Bookmarks are not shown in Microsoft Word by default; this minimises the risk of someone accidentally deleting or changing a bookmark. However, sometimes you do need to make them visible.
I had to do this recently, but really had difficulty finding the setting.
To enable the display of bookmarks, click on File and select Options.
In the Word Options window, select Advanced and scroll down to Show document content; the fifth setting down is Show bookmarks. Mark this setting and click OK:
I might not post many Excel snippets, but I’m collecting them into a small Excel Snippets series to make them easy to find.
I tend to do data manipulation in SQL Server rather than Excel, as it is generally easier to do it in SQL where I can run commands sequentially. However, there are times when I do need to do it in Excel.
I recently needed to replace one part of a string of text and really had trouble doing so. This was because I am used to using REPLACE in SQL and str_replace in PHP and expected the REPLACE function in Excel to allow similar replacement. However, I rapidly found that this was not the case.
I did some exploring and found that the command in Excel is actually SUBSTITUTE:
=SUBSTITUTE(A2,"Apples","Oranges")
This will replace the word apples with oranges in the string in cell A2.
I end up adding lots of words to the dictionary in Microsoft Office; often lots of vendor or product names. I recently added a misspelling of SmartConnect which I needed t remove and I had to do some research to find out how.
It’s not actually too complex to do, but there are a few screens you have to go through.
You can do this in any of the Office products; this example is done through Microsoft Word.
Open the Word Options from the File tab and select Proofing; click the Custom Dictionaries button in the middle of the window:
Continue reading “Remove Word From Microsoft Office Custom Dictionary”
I had a problem recently where the ump lists for Microsoft Office stopped updating. I did some checking around and a recommended solution I found is to remove large files (> 1MB) from the %AppData%\Microsoft\Windows\Recent\AutomaticDestinations.
I had a look in the folder and did find some large files and so deleted them. Over the next couple of hours, I checked the jump lists and they still weren’t updating, so I deleted the rest of the files to see if there was a smaller corrupt file.
The jump lists didn’t immediately start working, but I noticed after a few days that they were again recording recently accessed files.
I might not post many Excel snippets, but I’m collecting them into a small Excel Snippets series to make them easy to find.
A while ago, I posted an article on generating row umbers in a SQL script; I fielded a question from someone recently on doing the same thing Excel where the row number changes based on two columns of data. Basically when either of the columns were different, the row number should reset back to 1.
The following formula uses an IF combined with an OR to check if either column of data is different to the row above and if so sets the row number to 1 otherwise it increments by 1:
=IF(OR(A2<>A3,B2<>B3),1,C2+1)
A recent update to Microsoft Word saw the page background change from the white which has been used since forever; I’m assuming that I saw this change as I have office set to use the black theme. However, while I want the window black, I don’t want the page background to be black; I want it to continue being white.
There is a button on the action pane, on the View tab which allows you to toggle the view:
I might not post many Excel snippets, but I’m collecting them into a small Excel Snippets series to make them easy to find.
I’ve been doing work with quite a few clients with Jet Reports recently and one of the common requirements it to hide a row when values are blank. While the clients I am working with are experienced with Excel, they often are very good with VLOOKUP and other functions, but not so much with AND/OR operators.
This can be done using an IF statement combined with an AND operator. In Excel the and statement format is different to programming/scripting languages.
The below is an example AND checking the value in cell A2 and B2 and returning true if they are both 1 otherwise returning false:
=IF(AND(A2=1,B2=1),"True","False")
Just for completeness, the below is an example OR checking the value in cell A2 and B2 and returning true if they either cell is 1 otherwise returning false:
=IF(OR(A2=1,B2=1),"True","False")
I might not post many Excel snippets, but I’m collecting them into a small Excel Snippets series to make them easy to find.
I do far more manipulation of data in SQL scripts than I do in Excel, but sometimes need to accomplish something in Excel for clients. This time I was working with a client using Excel to generate a Microsoft Dynamics GP macro to update some costs.
We realised when testing that the decimal places were being lost by Excel so the macro contained 100 for £100 instead of the 100.00 which is needed for the macro to run successfully. I tired a couple of things and eventually found that TEXT can be used to retain the decimals.
The example, below, also includes a ROUND to reduce the result of a calculation to only two decimals.
=TEXT(ROUND(B2,2),".00")
My knowledge of using Excel is somewhat patchy; there are some things I can do easily and other simple things which I find troublesome. While I might not post many Excel snippets, I’m collecting them into a small series to make them easy to find again in future.
I’ve already been doing similar posts with Network Shell Snippets, PowerShell Snippets and VBA Snippets. The series index will automatically update as posts go live, but if you’re reading a syndicated version, you’ll need to check back to the original post
We recently had anew consultant join us at ISC Software which meant building a new laptop. As she lives fairly near me the laptop and other kit was delivered to me and I did some setup. Once she was logged on there was one unexpected issue with emails not sending in Outlook.
I did some checking of settings and found that there is a setting which needed to be enabled for sending of emails; I’m not sure why it was not set as I don’t recall having set it previously.
The setting is in (); select the Advanced tab and, under Send and receive, mark the Send immediately when connected checkbox:
Once the options were saved, emails were correctly sending.