Archive for the ‘Programming & Functions’ Category

JavaScript, PHP, and Regular Expressions for International and U.S. Phone Number Formats
I have been looking all over the web for a regular expression to use in JavaScript functions to help with formatting phone number links and printing phone numbers on pages for iPhones, Android, and other mobile devices. This process would naturally affect traditional internet browsers for traditional computers but I went down this path mostly for tracking phone number touch events in Google Analytics on only mobile devices. I finally had the epiphany today that created one expression that covered most international and United States phone number formats.
International and U.S. Phone Number Regular Expression
The red areas, represent the numbered portions. Areas in gray represent the options for spaces, dashes, periods, or sometimes nothing between numbers (e.g. the question marks).
/\d?(\s??\+?\.?)((\(\d{1,4}\))(\d{1,3})\s?)(\s??\.?)((\(\d{1,3}\))(\d{1,3})\s?)(\s??\.?)((\(\d{1,3}\))(\d{1,3})\s?)(\s??\.?)\d{3}(\.\s)\d{4}/ 
MS Excel Tip: Limiting Meta Descriptions Characters and Words Like Google
I found it hard to find this information on the web, so after a little bit of work I created an Excel formula that mimics the cut off point Google may naturally use when displaying your page’s Meta description in search engine results pages (SERPs). This can come in handy when you are optimizing your site for search engines (SEO). A well written page description is very helpful in improving a site’s click through rate. Depending on your page’s subject and target audience, writing a description in which the page’s story is incomplete or leaves a person hanging improves the searcher’s interest in clicking through. As a result, the last few words of a description could play a large part in creating that situation. But if Google cuts it off too soon or too late, it may be just enough to lose a person’s interest.
Here is the Formula:
=IF(LEN(A1)>155,LEFT(A1,FIND(“*”,SUBSTITUTE(A1,” “,”*”,LEN(LEFT(A1,154))LEN(SUBSTITUTE(LEFT(A1,154),” “,””))))) & IF(LEN(A1)>FIND(“*”,SUBSTITUTE(A1,” “,”*”,LEN(LEFT(A1,154))LEN(SUBSTITUTE(LEFT(A1,154),” “,””)))),”…”,””),A1)
Example:
Cell Contents: Everyone loves Google and wants to write just like them. Cutting off descriptions at the same spot as Google can help you write really great descriptions. Check out this awesome formula.
The Look in SERPs:
MS Excel Tip: Limiting Meta Descriptions Number of Characters
http://www.myspotinternetmarketing.com/msexceltiplimitmetadescriptionscharactersandwordslikegoogle
Everyone loves Google and wants to write just like them. Cutting off
descriptions at the same spot as Google can help you write really great …Behind the Formula
 The LEFT functions used in the internal portions of the formula grabs only the max amount of characters Google will show (154) and uses that throughout the formula for calculating.
 The formula then removes all the spaces and counts the character differences between the regular set and the one without spaces (this can also be used to count the number of words). This tells us how many spaces are in that 154 character portion of the description.
 Using that number, it replaces that final occurrence of the space with an asterisk using the SUBSTITUTE function (since it’s unlikely that an asterisk would be used in a description).
 Then using a FIND function, it counts the number of characters to that point of the asterisk.
 It uses the LEFT function to only return the characters up to the character count where the FIND function found the asterisk.
 And lastly, all of that only happens if the description is over 155 characters.
Use it well… 🙂

Excel Tidbits: Creating Column Header Rows for Sorting
Well, sad to say this one has stumped me for a long time. I work in Numbers from Apple’s iWorks Suite about as much as I do in Excel. Data sets above about 1000 rows, assuming use of processorheavy formulas like vlookups, really slow Numbers down. From a visual and formula perspective, Numbers is much easier to use. One of my favorite aspects is the ability to create header rows so that the last row becomes the column headers and the references in formulas. That makes modifying data quicker and improves one’s ability to keep track of a greater variety of segments.
However, when you’re dealing with tens of thousands of records/rows and multiple sheets, Excel excels (wink, wink) high above Numbers. Unfortunately, it is much harder to make things look professional in the program.
When I create dashboards, I usually want data summaries and pivot tables above the data, but it’s a pain when sorting the data below since typically you have to select the data you want to sort. Luckily the sorting options are saved even if your data selection changes. But it would still be much easier if I could just click a cell in a column and have Excel already know the data I want to sort and what I do not.
The solution is adding column filters (see image from the Mac Excel below).
 Select the column header row.
 Then under the “Data” menu or in the “Data” toolbar, select “Filter”.
 You don’t even need to use the filters. In fact, just remove them after adding them. From then on, Excel will know which row is the column headers when sorting. Brilliant!
 Now test the sort. Select a cell, or click a filter arrow on a column header to select a new sort for the column.
 Tip: If you remove the filters on the header row, be sure NOT to try sorting above that column header row. Doing so will retrain Excel to ignore the row and you’ll have to reassign the filters again.
No more selecting the cells you want to sort before so doing–saving mental energy; allowing that energy to focus on the task and not loose track of a formula you’re creating or a usecase scenario you’re trying to answer.
Want to see it in action? Download an example filter column header row spreadsheet to give it a try.
Best wishes!

Excel Tidbits: Extracting Domains from URLs Function
I rarely show how much of a techie I am, but thought this equation was valuable to share. In some of the work I do for my company, Anthem Optimization, I mash and extract data daily using Excel. I needed a way to pull out the domain from a web address and compare it with domains from other sheets and activities. This then gives me insight into how often we do work on websites across all our projects.
Version Note: The formula below only works in newer versions of Excel–typically versions that can read the xlsx format. Older versions of Excel (pre2004) are limited to seven nested formulas. This equation has eight. If you need this to work in an older version, you can remove the over arching check for information in the $A6 cell at the very front (if($A6=””,…)). The iserror formulas in the if statements checks for errors and displays whatever is in the A6 cell if an error occurs. If the cell is blank, it should stay blank. I simply used the blank check upfront to make the formula more efficient in processing.
Here is the formula:
=IF($A6=””,””,SUBSTITUTE(IF(ISERROR(FIND(“https://”,$A6)),SUBSTITUTE(IF(ISERROR(FIND(“/”,SUBSTITUTE($A6,”http://”,””))),$A6,REPLACE($A6,FIND(“/”,SUBSTITUTE($A6,”http://”,””))+7,LEN($A6)+1,””)),”http://”,””),SUBSTITUTE(IF(ISERROR(FIND(“/”,SUBSTITUTE($A6,”https://”,””))),$A6,REPLACE($A6,FIND(“/”,SUBSTITUTE($A6,”https://”,””))+8,LEN($A6)+1,””)),”https://”,””)),”www.”,””)
A bit complex to explain, but it considers situations where a web address uses either the secure “https” protocol or just the regular “http” and always removes the “www” from the address. The rest of it is looking for the first slash after the “http://” or “https://” and deletes everything from that slash to the end.
Here are the details for each stage:
=IF($A6=””,””, // Makes sure nothing happens if nothing is in the cell
SUBSTITUTE( // Used to delete www from the result
IF(ISERROR(FIND(“https://”,$A6)), // If it doesn’t find https, then execute the function to remove everything after the domain for sites with http:// — this is needed since the s adds characters prior to the slash (important for the replace function later)
SUBSTITUTE( // If https:// was not found, this begins the formula used to delete everything after the first slash after the http://
IF(ISERROR(FIND(“/”, SUBSTITUTE($A6,”http://”,””))), // If it doesn’t find a slash after the domain, then do nothing and display what’s in the cell
$A6, // Just display what was in the cell–which should be just a domain address since there is no slash after the http
REPLACE($A6, // This is the core function that removes everything after the domain
FIND(“/”, SUBSTITUTE($A6,”http://”,””)) // Finds the spot/character number where the first slash is after the http://
+ 7, // Adds the number of spaces for http so that the starting point of the replacement is correct
LEN($A6)+1, //This finds the end of the space where text is supposed to be replaced/deleted. Added 1 to cover bases and potential spacing issues.
“” // this is what it will be replaced with…nothing.
) // Ends the Replace function.
), // Ends the IsError function.
“http://”,”” // Once the result is determined (either after the end of the url is cut off or if it’s just a domain), delete the http:// so that just the domain shows
), // Ends the substitute function if http:// is found.
SUBSTITUTE( // If https:// was not found, this begins the formula used to delete everything after the first slash after the http://
IF(ISERROR(FIND(“/”, SUBSTITUTE($A6,”https://”,””))), // If it doesn’t find a slash after the secure domain, then do nothing and display what’s in the cell
$A6, // Just display what was in the cell–which should be just a domain address since there is no slash after the https://
REPLACE($A6, // This is the core function that removes everything after the secure domain
FIND(“/”, SUBSTITUTE($A6,”https://”,””)) // Finds the spot/character number where the first slash is after the https://
+ 8, // Adds the number of spaces for https so that the starting point of the replacement is correct
LEN($A6)+1, //This finds the end of the space where text is supposed to be replaced/deleted. Added 1 to cover bases and potential spacing issues.
“” // this is what it will be replaced with…nothing.
) // Ends the Replace function.
), // Ends the IsError function.
“https://”,”” // Once the result is determined (either after the end of the url is cut off or if it’s just a domain), delete the https:// so that just the domain shows
), // Ends the substitute function if https:// is found.
),
“www.”,”” // so whatever the result is, it should now just be a domain, but sometimes links have www for a site and sometimes not. So, we’ll delete the www if it’s there.
)
There are many great applications for this in search engine optimization (SEO), online marketing, publicity, analytics, web development, and many more areas where reporting and comparing data is helpful! Combine this with a VLOOKUP or SUMIF formula and you will be able to total up visits, backlinks, status, errors, and much more.