Microsoft Excel

Microsoft Excel: How to Count Number of Spaces in Cell String

388 321 rhecht

excel

B1: =LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))

Notice the space there. You can replace that with any other letter to see how many instances of that character are present. It’s simple and genius.

Excel on Mac – How to Delete Contents of Multiple Cells

256 256 rhecht

excel_logo

fn+delete

Also, you can do control+B

I know, with Excel on Windows it’s easier 🙂

Kudos to ExcelJet for this gem.

Excel – How to Remove Multiple Hyperlinks by Using a Macro

150 150 rhecht

excel_logoI’ve seen many other places on the internet where an item doesn’t show up, requires a plugin, or that the version of Microsoft Office isn’t compatible. This macro solution actually works.

Step 1. Press Alt + F11 to start the Visual Basic Editor.

Step 2. Double click the workbook you are using on the Project Explorer and type the following code:

Sub RemoveHyperlinks() 
ActiveSheet.Hyperlinks.Delete 
End Sub

Step 3. Save your work. Note: if you’re using Excel 2007 or higher it may ask you to save the workbook as .xlsm. If this is only a one-time thing you should just save your workbook “as is.”

Step 4. Run the macro by pressing Alt + F8 or using the menu by View > Macros

Step 5. Select the macro you have made, it should have the name ‘RemoveHyperlinks’.

Math/Excel – How to Calculate Percentage Change

150 150 rhecht

I am working on graphs and I need to find the percentage of increase from one figure to another. How do I do that? For instance staff hours for 2006 were 28011 and for 2007 were 31230. How do I find the percentage of increase?

Percentage change is calculated this way:

percentchange_1.1

The sign indicates whether it is a decrease or increase. For your example,

percentchange_1.2

  • Since this is a positive figure, it is an 11.5% increase.

    Hope this helps!

    Source: http://mathcentral.uregina.ca/QQ/database/QQ.09.06/s/galland1.html

 

Excel: How to Replace Text in a Cell

150 150 rhecht

How to Replace Text in a Cell in Excel?

Use the Substitute(text, old_text, new_text, [instance_num]) function.

This has saved me a lot of time when formatting raw data.

How to Set Up Domain Mapping for WordPress Multisite

150 150 rhecht

So you’ve gone through setting up WordPress Multisite, and now you’re ready to start setting up new sites in your network. Only trouble is, you want to use unique domains for your sites instead of using subdomains of your main installation. Well, with a little extra effort, it’s entirely possible.

Install the required plugin

There’s an excellent plugin that will do most of the hard work for you. Install the WordPress MU Domain Mapping plugin and Network Activate the plugin.

Before you proceed any further, there’s a couple of things that you have to do manually. Access your site’s installation, either using FTP or your hosting Control Panel and go to wp-content/plugins/wordpress-mu-domain-mapping/ and locate the sunrise.php file. You must move this file up two levels, to the wp-content folder.

Once you’ve done that, find your wp-config.php file and add the following definition. Add it below your previous multisite rules (i.e. above the “Stop editing” line):

define( 'SUNRISE', 'on' );

Domain-Mapping.php
Move “domain-mapping.php” to the mu-plugins” folder at the wp-content “root” level. This is important as certain web servers won’t render the mapped URLs correctly unless this is done.

Configure the plugin

Now your plugin is correctly installed and when you head to the Network Admin area you’ll notice a menu under Settings called Domain Mapping. Click on that so that you can configure the plugin.
The main thing you need to be concerned with is setting the IP address of your server. To find this, log in to your hosting cPanel and on the main page, you should be able to see the IP address of your server in the information on the left-hand side. If you can’t find the IP address, you can get in touch with your host, or use one of many available free tools to convert the web address to an IP address.

If you fill out the IP address, you can ignore the CNAME field. Then, the remaining options are as follows:

  1. Remote Login – Means that logging in to one site will log you in to all sites.
  2. Permanent Redirect – This will create a 301 redirect on your subdomain (as opposed to a temporary 302 redirect) – highly recommended if the domain mapping will be permanent.
  3. User Domain Mapping Page – Creates a new menu in each site which allows that site’s users to map their own domains (instead of being controlled by the network’s Super Admin only).
  4. Redirect administration pages – Means that all administration pages will be kept on the original site’s domain, instead of on the mapped domains.
  5. Disable primary domain check – Means that multiple domains pointing to a single site will be treated as separate sites, instead of redirecting all domains to the noted primary domain (not recommended).

Map domains to your network sites

Now you’re ready to set up the domain for your new sites. You should have already created the site that you’re wanting to map your domain to. At the moment, it will just be a subdomain of your main site – it doesn’t have to be set up or looking perfect – it just has to exist.

Once you’ve purchased your domain, make sure that you set the nameservers for the domain to your hosting account’s nameservers. Then, go to your hosting account cPanel (which all good hosts, including HostGator, should have) and find the Parked Domains option under Domains.

Once in there, add your new domain as a parked domain, making sure that the document root shows as the same folder as your WordPress installation (probably /public_html). If you can’t do this (because you don’t have cPanel for instance), you need to set an A record on your new domain that points to the IP address of your hosting server (which you found out earlier).

Now is the final step. You’ll need to know the “Site ID” of the site you want to map the domain to. If you click on Sites > Sites in the Network Admin, and click on the name of the site (as if to edit it), the URL will include the site ID. It will look something like this (where the Site ID is 2 in the example):

http://wpsites.rafihecht.com/wp-admin/network/site-info.php?id=2

Now back in your Network Admin, go to Domains under the Settings Menu. You’ll see a section for adding a new domain, with three fields: one for the site ID, which you just found out, the domain name and an option whether to set the domain as the primary domain for the site. This is because you can set more than one domain to point to a single site, so you want to specify which one should be the site’s primary domain.

Once you save that, with any luck (and assuming that DNS changes have propagated), if you go to your new domain in a web browser, you should find yourself at your new site.

I’ll grant you that it is a little bit fiddly, and it would be nice if WordPress would bring this into the core and make it a bit more of a fluid process, but once it is set up, you never need to look at it again; the plugin will keep serving up the right site at the right domain.

Did you manage to get through the setup process? Did you encounter any problems? If you need any help getting through this process, you can always ask a question here, or hire me to do it for you.

 

Content of this part of the blog post was courtesy of : http://www.doitwithwp.com/domain-mapping-for-wordpress-multisite/

Remove All Hyperlinks in Microsoft Word or Excel

150 150 rhecht

Word

1. CTRL+A and then CTRL+SHIFT+F9

2. Credit to http://www.tech-recipes.com/rx/1323/remove-all-hyperlinks-in-word-or-excel/

Go to “Insert” > “Module” and in the pop-up window copy:


Sub RemoveHyperlinks()
Dim oField As Field
For Each oField In ActiveDocument.Fields
If oField.Type = wdFieldHyperlink Then
oField.Unlink
End If
Next
Set oField = Nothing
End Sub

Then click “File” > Close and return to Microsoft Word

You can now run the Macro in Word by going to:

Tools > Macro > Macro and then Run “RemoveAllHyperlinks”

Excel

You can do the same in an Excel Document:

Hit [ALT]+[F11] to open the Visual Basic Editor

Go to “Insert” > “Module” and in the pop-up window copy:

Sub RemoveHyperlinks()
'Remove all hyperlinks from the active sheet
ActiveSheet.Hyperlinks.Delete
End Sub

Then click “File” > Close and return to Microsoft Excel

You can now run the Macro in Excel by going to:

Tools > Macro > Macro and then Run “RemoveAllHyperlinks”, this will delete all URLS on the selected worksheet.

Excel: My Spreadsheet Has a Font?

150 150 rhecht

By default, Microsoft Office Excel 2007 uses the Body Font font in font size 11 (which is displayed as the Calibri font in font size 11 when you type data in a worksheet, header or footer, or text box) but you can change the default font and font size for all new workbooks that you create.

  • Click the Microsoft Office Button , and then click Excel Options.
  • In the Popular category, under When creating new workbooks, do the following:
  • In the Use this font box, click the font that you want to use.
  • In the Font Size box, enter the font size that you want to use.

NOTE: To begin using the new default font and font size, you must restart Excel. The new default font and font size are used only in new workbooks that you create after you restart Excel; existing workbooks are not affected. To use the new default font, you can move worksheets from an existing workbook to a new workbook. For more information, see Move or copy a worksheet.

Courtesy of: Microsoft Office Knowledge Base

For Word 2003 and Earlier:

Step1
Open the “Options” dialog box. Click on “Tools” on the tool bar and select “Options.” This opens the Options dialog box in which you can change the default attributes of your workbook.

Step 2
Change the default font. Click the “General” tab and then click the arrow in the “Standard font” field. Select a new font from the list.

Step 3
Change the font size. Click the “Size” box and select a new font size.

Step 4
Click “OK” when you have finished making your changes.

You will have to restart Excel in order for the changes to take effect.

This post was inspired by the Nick Burns video with Jackie Chan on “My Spreadsheet has a font?”

Removing All Hyperlinks in Excel

150 150 rhecht

Removing a bunch of hyperlinks can be tedious. Imagine the time you have to spend right-clicking on each of those links and selecting ‘remove hyperlink’ on the context menu. Of course it would just take two clicks, but what if you are to remove a hundred of them or perhaps 1000 hyperlinks? I’m sure that would be enough to scrape out the paint of your right-mouse button.

Below are some simple techniques you can use in order to remove single to multiple hyperlinks on a page:

Removing just one hyperlink

1. Right-Click on the cell and select Remove Hyperlink from the context menu.

Removing hyperlinks using the cell format

1. Select the cells having hyperlinks. You can use CTRL+LEFT CLICK to randomly select a cell.

2. On the menu click on Edit. Hover your mouse to the option Clear and select Fomats. Please note that the cell is still clickable to remove the hyperlink completely you have to select Clear->All.

Remove Excel hyperlinks on selected cells the better way

1. Type in any text or number in a blank cell

2. Right-click and select Copy on the context menu.

3. While pressing CTRL, select each cell with the hyperlink you wish to be removed

4. On the Edit menu, select Paste Special.

5. Under Operation, click Multiply and then click OK.

6. If you are using Office 2007, you may need to click or change the cell format to Normal found at the Styles Group Toolbar.

Removing Excel hyperlinks using a macro

Assuming you know excel programming you could create a macro to automatically remove the hyperlinks.

1. Start Visual Basic Editor. Alternatively you can press ALT-F11 to start the editor.

2. Double click the workbook you are using on the Project Explorer.

3. Type the following text:

Sub RemoveHyperlinks()

ActiveSheet.Hyperlinks.Delete

End Sub

4. Save your work.

5. Run your macro by pressing ALT-F8 or using the menu by Tools->Macro->Macros

6. Select the macro you have made, it should have the name ‘RemoveHyperlinks’.

Removing the Default Hyperlink Option

The most efficient way to remove them is by actually avoiding them in the first place. Now you don’t need to follow those hard and confusing steps above anymore. You can prevent Excel from formatting URLs and network links by removing its AutoFormat function at the AutoCorrect dialog box.

Unfortunately, if you are using Office 2003 or lower, i can’t give you the exact steps but the AutoCorrect is at Tools on the main menu bar. I just don’t remember where to go from there. If you are using Office 2000, there is no way we could prevent URLs from being formatted.

Post courtesy of: http://www.tildemark.com/removing-multiple-hyperlinks-in-excel/

Excel Macro – Get and Extract Hyperlink URL Address From Link

150 150 rhecht

This has personally worked like a charm for me.

Try this user-defined function:

=ShowAddress(A1)

Where A1 is the cell with the hyperlink in it. Here is the Macro function code:


Public Function ShowAddress(rng As Range) As String
If rng.Cells.Count > 1 Then
ShowAddress = CVErr(xlErrValue)
Else
ShowAddress = rng.Hyperlinks.Item(1).Address
End If
End Function

Note: With Excel 2007 and 2010 you first need to save a document as “.xlsm” to enable macros!